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
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
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
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
January 21, 2014 at 7:44 am
sql_ques (1/21/2014)
NULL NULL NULL NULL 646April 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.
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:
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
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://qa.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