October 1, 2012 at 2:04 pm
I have a rollup issue where I want to add in a varchar status field. This is coming from a View where the status is coming back as text. But if I add it to the Group By Rollup() line, it changes the grouping and totaling.
IF OBJECT_ID('tempdb..#tblPopulation') IS NOT NULL
BEGIN
DROP TABLE #tblPopulation
END
CREATE TABLE #tblPopulation (
Country VARCHAR(100),
[State] VARCHAR(100),
City VARCHAR(100),
[Population (in Millions)] int,
[# Programmers (in hundreds)] int,
STATUS varchar(50)
)
INSERT INTO #tblPopulation VALUES('India', 'Delhi','East Delhi',15,9 , 'In Progress')
INSERT INTO #tblPopulation VALUES('India', 'Delhi','South Delhi',200,8, 'In Progress')
INSERT INTO #tblPopulation VALUES('India', 'Delhi','North Delhi',110,5.5, 'Initial')
INSERT INTO #tblPopulation VALUES('India', 'Delhi','West Delhi',25,7.5, 'Processed')
INSERT INTO #tblPopulation VALUES('India', 'Karnataka','Bangalore',95,9.5, 'In Progress')
INSERT INTO #tblPopulation VALUES('India', 'Karnataka','Belur',82,2, 'In Progress')
INSERT INTO #tblPopulation VALUES('India', 'Karnataka','Manipal',150,19, 'Processed')
INSERT INTO #tblPopulation VALUES('India', 'Maharastra','Mumbai',22,30, 'In Progress')
INSERT INTO #tblPopulation VALUES('India', 'Maharastra','Pune',110,20, 'Inital')
INSERT INTO #tblPopulation VALUES('India', 'Maharastra','Nagpur',39,11, 'Processed')
INSERT INTO #tblPopulation VALUES('India', 'Maharastra','Nashik',72,18, 'In Progress')
-- Grouping correct here with no status
SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)],
SUM ([# Programmers (in hundreds)]) AS [# Programmers (in hundreds)]
FROM #tblPopulation
GROUP BY ROLLUP(Country,[State],City)
-- Grouping messed up by adding the Status.
SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)],
SUM ([# Programmers (in hundreds)]) AS [# Programmers (in hundreds)],
Status
FROM #tblPopulation
GROUP BY ROLLUP(Country,[State],City), Status
The result of the 1st query
CountryStateCityPopulation (in Millions)# Programmers (in hundreds)
IndiaDelhiEast Delhi159
IndiaDelhiNorth Delhi1105
IndiaDelhiSouth Delhi2008
IndiaDelhiWest Delhi257
IndiaDelhiNULL35029
IndiaKarnatakaBangalore959
IndiaKarnatakaBelur822
IndiaKarnatakaManipal15019
IndiaKarnatakaNULL32730
IndiaMaharastraMumbai2230
IndiaMaharastraNagpur3911
IndiaMaharastraNashik7218
IndiaMaharastraPune11020
IndiaMaharastraNULL24379
IndiaNULLNULL920138
NULLNULLNULL920138
The result of the 2nd query
CountryStateCityPopulation (in Millions)# Programmers (in hundreds)Status
IndiaDelhiEast Delhi159In Progress
IndiaDelhiSouth Delhi2008In Progress
IndiaDelhiNULL21517In Progress
IndiaKarnatakaBangalore959In Progress
IndiaKarnatakaBelur822In Progress
IndiaKarnatakaNULL17711In Progress
IndiaMaharastraMumbai2230In Progress
IndiaMaharastraNashik7218In Progress
IndiaMaharastraNULL9448In Progress
IndiaNULLNULL48676In Progress
NULLNULLNULL48676In Progress
IndiaMaharastraPune11020Inital
IndiaMaharastraNULL11020Inital
IndiaNULLNULL11020Inital
NULLNULLNULL11020Inital
IndiaDelhiNorth Delhi1105Initial
IndiaDelhiNULL1105Initial
IndiaNULLNULL1105Initial
NULLNULLNULL1105Initial
IndiaDelhiWest Delhi257Processed
IndiaDelhiNULL257Processed
IndiaKarnatakaManipal15019Processed
IndiaKarnatakaNULL15019Processed
IndiaMaharastraNagpur3911Processed
IndiaMaharastraNULL3911Processed
IndiaNULLNULL21437Processed
NULLNULLNULL21437Processed
Is there a way to add the status to the query?
For percentages or other numbers I don't want summed, I usually just use MAX(). But can't do that with a varchar.
Thanks,
Tom
October 1, 2012 at 2:21 pm
Just a guess but why wouldn't you put the [status] column into the rollup clause if you wanted to use both the rollup clase and the [status] column?
October 1, 2012 at 2:36 pm
The problem is that if I add the status into the Group By clause, even inside the ROLLUP function, it uses the status as part of the grouping - which I don't want. As you can see the groupings get all messed up. You end up with totals for each line.
The status is just a comment for that detail line that I need to pass back to the application.
Here it is putting the status in the ROLLUP function:
-- Grouping messed up by adding the Status into the Rollup statement.
SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)],
SUM ([# Programmers (in hundreds)]) AS [# Programmers (in hundreds)],
Status
FROM #tblPopulation
GROUP BY ROLLUP(Country,[State],City,status)
Results:
IndiaDelhiEast Delhi159In Progress
IndiaDelhiEast Delhi159NULL
IndiaDelhiNorth Delhi1105Initial
IndiaDelhiNorth Delhi1105NULL
IndiaDelhiSouth Delhi2008In Progress
IndiaDelhiSouth Delhi2008NULL
IndiaDelhiWest Delhi257Processed
IndiaDelhiWest Delhi257NULL
IndiaDelhiNULL35029NULL
IndiaKarnatakaBangalore959In Progress
IndiaKarnatakaBangalore959NULL
IndiaKarnatakaBelur822In Progress
IndiaKarnatakaBelur822NULL
IndiaKarnatakaManipal15019Processed
IndiaKarnatakaManipal15019NULL
IndiaKarnatakaNULL32730NULL
IndiaMaharastraMumbai2230In Progress
IndiaMaharastraMumbai2230NULL
IndiaMaharastraNagpur3911Processed
IndiaMaharastraNagpur3911NULL
IndiaMaharastraNashik7218In Progress
IndiaMaharastraNashik7218NULL
IndiaMaharastraPune11020Inital
IndiaMaharastraPune11020NULL
IndiaMaharastraNULL24379NULL
IndiaNULLNULL920138NULL
NULLNULLNULL920138NULL
Thanks,
Tom
October 1, 2012 at 2:43 pm
i'm thinking that you need a case statement plus a min, ot get a status/status indicator.
you cannot use alphabetical order of the statuses, because In Progress comes before Initial
maybe change to an integer, and a case statement back out again?
SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)],
SUM ([# Programmers (in hundreds)]) AS [# Programmers (in hundreds)],
MIN(CASE WHEN [STATUS] = 'Initial' THEN 0
WHEN [STATUS] = 'In Progress' THEN 1
WHEN [STATUS] = 'Processed' THEN 2
END) As Status
FROM #tblPopulation
GROUP BY ROLLUP(Country,[State],City)
Lowell
October 1, 2012 at 3:28 pm
That might work ok in this situation, where I only have 3 possible values.
Sorting doesn't matter as I am not going to sort by this one.
It wouldn't work as well if there are many comments. Since it is hardcoded, it could be a problem if comments or statuses change.
Thanks,
Tom
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply