Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Column Name for NULLs Expand / Collapse
Author
Message
Posted Tuesday, January 21, 2014 4:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 8:22 AM
Points: 49, Visits: 297
Hi,

How do I add column names as Total and SubTotal for NULL values.
SELECT DISTINCT
--[Group]
[Month]
,[Market]
,[Environment]
,[type]
, COUNT(*)
FROM (SELECT DISTINCT

[systemname] AS 'Market'
,CASE ([U_L]) WHEN 'Live' THEN 'L'
ELSE 'U' END AS 'Environment'
,[Group] AS [Group]
,[type] AS [Type]
,DATENAME (mm, [StartDate]) As [Month]

FROM [TableName]
WHERE
[Group] IN ('Services', 'Leads') AND
StartDate BETWEEN ('20131101') AND ('20140120')
) AS qry

--WHERE [Type] IS NOT NULL
GROUP BY
--[Group]
[Month]
,[Market]
,[Environment]
,[Type]
WITH ROLLUP

Thanks for help.
Post #1532956
Posted Tuesday, January 21, 2014 4:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 12,206, Visits: 9,169
It would be helpful if you showed us what this query returns and what your desired result is.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1532957
Posted Tuesday, January 21, 2014 7:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 8:22 AM
Points: 49, Visits: 297
The output I require is
Month Market Environment Releasetype ID
Grand Total 646
April 60
Bank Total 1
LIVE 1
Web 1
Test Total 1
LIVE 1
SQL DB 1
Post #1533065
Posted Tuesday, January 21, 2014 7:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 12,206, Visits: 9,169
What does the query currently return?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1533068
Posted Tuesday, January 21, 2014 7:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 8:22 AM
Points: 49, Visits: 297
NULL NULL NULL NULL 646
April NULL NULL NULL 60
April Agricultural NULL NULL 1
April Agricultural LIVE NULL 1
April Agricultural LIVE Web 1
April Database NULL NULL 1
April Database LIVE NULL 1
Post #1533071
Posted Tuesday, January 21, 2014 7:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 12,206, Visits: 9,169
sql_ques (1/21/2014)
NULL NULL NULL NULL 646
April NULL NULL NULL 60
April Agricultural NULL NULL 1
April Agricultural LIVE NULL 1
April Agricultural LIVE Web 1
April Database NULL NULL 1
April Database LIVE NULL 1


And how exactly would you like to go from this result set to the other? They have almost nothing in common.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1533079
Posted Tuesday, January 21, 2014 7:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 8:22 AM
Points: 49, Visits: 297
Output should be:
GrandTotal 646
April MonthTotal 60
April Agricultural Total 1
April Agricultural LIVE NULL 1
April Agricultural LIVE Web 1
April Database Total 1
April Database LIVE NULL 1
Post #1533081
Posted Tuesday, January 21, 2014 7:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1533088
Posted Wednesday, January 22, 2014 12:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 12,206, Visits: 9,169
You can use GROUPING or GROUPING_ID to check if a row is a detail row or a total.





How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1533484
Posted Thursday, January 23, 2014 9:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 7:40 AM
Points: 346, Visits: 1,065
Not sure if this will do what you need, but there is an example in this stairway article for replacing NULL with a string while doing a ROLLUP.

http://www.sqlservercentral.com/articles/Stairway+Series/87629/
Post #1534167
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse