January 21, 2014 at 4:16 am
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.
January 21, 2014 at 4:18 am
It would be helpful if you showed us what this query returns and what your desired result is.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 21, 2014 at 7:35 am
The output I require is
MonthMarketEnvironmentReleasetypeID
Grand Total 646
April 60
Bank Total 1
LIVE1
Web1
TestTotal1
LIVE1
SQL DB1
January 21, 2014 at 7:39 am
What does the query currently return?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 21, 2014 at 7:42 am
NULLNULLNULLNULL 646
AprilNULLNULLNULL60
AprilAgricultural NULLNULL1
AprilAgricultural LIVENULL1
AprilAgricultural LIVEWeb1
AprilDatabaseNULLNULL1
AprilDatabaseLIVENULL1
January 21, 2014 at 7:44 am
sql_ques (1/21/2014)
NULLNULLNULLNULL 646AprilNULLNULLNULL60
AprilAgricultural NULLNULL1
AprilAgricultural LIVENULL1
AprilAgricultural LIVEWeb1
AprilDatabaseNULLNULL1
AprilDatabaseLIVENULL1
And how exactly would you like to go from this result set to the other? They have almost nothing in common.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 21, 2014 at 7:45 am
Output should be:
GrandTotal646
AprilMonthTotal60
AprilAgricultural Total1
AprilAgricultural LIVENULL1
AprilAgricultural LIVEWeb1
AprilDatabaseTotal1
AprilDatabaseLIVENULL1
January 21, 2014 at 7:53 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 22, 2014 at 12:17 am
You can use GROUPING or GROUPING_ID to check if a row is a detail row or a total.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 23, 2014 at 9:38 am
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/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply