September 28, 2012 at 6:43 pm
I cannot figure out how to add columns or ordering to my query which has a Rollup without it changing where the subtotals are done.
There are no PKs or joins as this would come from a view. So I created a new a table to look like the view. The data is just put together to show the issue.
Here is the Schema:
IF OBJECT_ID('tempdb..#tblPopulation') IS NOT NULL
BEGIN
DROP TABLE #tblPopulation
CREATE TABLE #tblPopulation (
Country VARCHAR(100),
[State] VARCHAR(100),
City VARCHAR(100),
DisplayOrder int,
SegmentID int,
[Population (in Millions)] int,
[# Programmers (in hundreds)] int,
[Percentage of All Careers] decimal(12,2)
)
INSERT INTO #tblPopulation VALUES('India', 'Delhi','East Delhi',3,15,9 , 12, 15.25)
INSERT INTO #tblPopulation VALUES('India', 'Delhi','South Delhi',4,200,8, 8, 12.20)
INSERT INTO #tblPopulation VALUES('India', 'Delhi','North Delhi',2,110,5.5,15, 30.00)
INSERT INTO #tblPopulation VALUES('India', 'Delhi','West Delhi',1,25,7.5, 18, 11.00)
INSERT INTO #tblPopulation VALUES('India', 'Karnataka','Bangalore',11,95,9.5, 23, 18.09)
INSERT INTO #tblPopulation VALUES('India', 'Karnataka','Belur',10,82,2.5,23, 21.10)
INSERT INTO #tblPopulation VALUES('India', 'Karnataka','Manipal',9,150,1.5, 19, 31.30)
INSERT INTO #tblPopulation VALUES('India', 'Maharastra','Mumbai',6,22,30, 24, 25.10)
INSERT INTO #tblPopulation VALUES('India', 'Maharastra','Pune',5,110,20, 25, 45.10)
INSERT INTO #tblPopulation VALUES('India', 'Maharastra','Nagpur',7,39,11, 12, 19.00)
INSERT INTO #tblPopulation VALUES('India', 'Maharastra','Nashik',8,72,6.5, 18, 29.10)
END
SELECT * FROM #tblPopulation
SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)]
FROM #tblPopulation
GROUP BY Country,[State],City WITH ROLLUP
SELECT SegmentID,Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)]
FROM #tblPopulation
GROUP BY Country,[State],SegmentId,City WITH ROLLUP
SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)],
SUM ([# Programmers (in hundreds)]) AS [# Programmers (in hundreds)]
FROM #tblPopulation
GROUP BY Country,[State],SegmentId,City WITH ROLLUP
The 2nd select is what I wanted to see. I am not sure why it doesn't subtotal the cities but that is actually what I want to happen.
CountryStateCityPopulation (in Millions)
IndiaDelhiEast Delhi9
IndiaDelhiNorth Delhi5
IndiaDelhiSouth Delhi8
IndiaDelhiWest Delhi7
IndiaDelhiNULL29
IndiaKarnatakaBangalore9
IndiaKarnatakaBelur2
IndiaKarnatakaManipal1
IndiaKarnatakaNULL12
IndiaMaharastraMumbai30
IndiaMaharastraNagpur11
IndiaMaharastraNashik6
IndiaMaharastraPune20
IndiaMaharastraNULL67
IndiaNULLNULL108
NULLNULLNULL108
As you can see, it is totalling by State, which is what I want.
But now I want to add some other columns without changing the subtotaling. I tried to add an ID passed back that will be used in my grid. But as soon as I add it, it now starts to subtotal by city and now each row is subtotaled (3rd query).
SegmentIDCountryStateCityPopulation (in Millions)
15IndiaDelhiEast Delhi9
15IndiaDelhiNULL9
25IndiaDelhiWest Delhi7
25IndiaDelhiNULL7
110IndiaDelhiNorth Delhi5
110IndiaDelhiNULL5
200IndiaDelhiSouth Delhi8
200IndiaDelhiNULL8
NULLIndiaDelhiNULL29
82IndiaKarnatakaBelur2
82IndiaKarnatakaNULL2
95IndiaKarnatakaBangalore9
95IndiaKarnatakaNULL9
150IndiaKarnatakaManipal1
150IndiaKarnatakaNULL1
NULLIndiaKarnatakaNULL12
22IndiaMaharastraMumbai30
22IndiaMaharastraNULL30
39IndiaMaharastraNagpur11
39IndiaMaharastraNULL11
72IndiaMaharastraNashik6
72IndiaMaharastraNULL6
110IndiaMaharastraPune20
110IndiaMaharastraNULL20
NULLIndiaMaharastraNULL67
NULLIndiaNULLNULL108
NULLNULLNULLNULL108
The problem here is that I don't want it apply at all to the rollup as it is just a label I want passed back.
In the last query, I am just wanting to add another column that is summed and totaled by State but I get it now totaled by city as in the last case. In my case, I would actually have about 10 columns that are summed.
CountryStateCityPopulation (in Millions)# Programmers (in hundreds)
IndiaDelhiEast Delhi912
IndiaDelhiNULL912
IndiaDelhiWest Delhi718
IndiaDelhiNULL718
IndiaDelhiNorth Delhi515
IndiaDelhiNULL515
IndiaDelhiSouth Delhi88
IndiaDelhiNULL88
IndiaDelhiNULL2953
IndiaKarnatakaBelur223
IndiaKarnatakaNULL223
IndiaKarnatakaBangalore923
IndiaKarnatakaNULL923
IndiaKarnatakaManipal119
IndiaKarnatakaNULL119
IndiaKarnatakaNULL1265
IndiaMaharastraMumbai3024
IndiaMaharastraNULL3024
IndiaMaharastraNagpur1112
IndiaMaharastraNULL1112
IndiaMaharastraNashik618
IndiaMaharastraNULL618
IndiaMaharastraPune2025
IndiaMaharastraNULL2025
IndiaMaharastraNULL6779
IndiaNULLNULL108197
NULLNULLNULL108197
In both of these queries, I don't know why it decides to total by city and the 2nd query didn't.
There are 2 other cases that I haven't added as I can't figure out how to even set up the query.
One is I would like to see how to order it by a different order without changing the subtotals by State. The DisplayOrder is set up to order different from alpha within each state. I can't seem to get this to work.
The other one has to do with the Percentage. It would be like the PK where I just want it to display and I would handle it in code by using the total by another value that I would supply for total jobs.
Thanks,
Tom
September 30, 2012 at 12:25 pm
I figured out part of the problem. I can only have Country, State and City in the Group By Clause.
So I can now just keep adding each aggregated column.
SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)],
SUM ([# Programmers (in hundreds)]) AS [# Programmers (in hundreds)],
MAX([Percentage of All Careers]),
Max(SegmentID)
FROM #tblPopulation
GROUP BY Country,[State],City WITH ROLLUP
CountryStateCityPopulation (in Millions)# Programmers (in hundreds)(No column name)(No column name)
IndiaDelhiEast Delhi91215.2515
IndiaDelhiNorth Delhi51530.00110
IndiaDelhiSouth Delhi8812.20200
IndiaDelhiWest Delhi71811.0025
IndiaDelhiNULL295330.00200
IndiaKarnatakaBangalore92318.0995
IndiaKarnatakaBelur22321.1082
IndiaKarnatakaManipal11931.30150
IndiaKarnatakaNULL126531.30150
IndiaMaharastraMumbai302425.1022
IndiaMaharastraNagpur111219.0039
IndiaMaharastraNashik61829.1072
IndiaMaharastraPune202545.10110
IndiaMaharastraNULL677945.10110
IndiaNULLNULL10819745.10200
NULLNULLNULL10819745.10200
I used MAX for the Percentage field just to get it into the column. I can just ignore the MAX in the total column in my code.
But I can't figure out how to get this to order by the DisplayOrder column.
I tried:
SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)],
SUM ([# Programmers (in hundreds)]) AS [# Programmers (in hundreds)],
MAX([Percentage of All Careers]),
Max(SegmentID),
MAX(DisplayOrder)
FROM #tblPopulation
GROUP BY Country,[State],City WITH ROLLUP
ORDER BY DisplayOrder
and get an error:
Column "#tblPopulation.DisplayOrder" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
But it is in the Max function.
I need to get a different order for the city within the state, which is why I have the DisplayOrder field. The rollup uses alpha sort.
Thanks,
Tom
September 30, 2012 at 2:13 pm
I was able to get this to work using combination of Rank, Row_Number.
I also made sure that the totals were on the bottom using a Case Statement.
Here is what I came up with.
SELECT Country, [STATE], City, [Population (in Millions)], [# Programmers (in hundreds)],PCTCareers,SegmentId,DisplayOrder,
CountryType, RowNumber, RankedByState
FROM (
SELECT Country,[State],City,
SUM ([Population (in Millions)]) AS [Population (in Millions)],
SUM ([# Programmers (in hundreds)]) AS [# Programmers (in hundreds)],
MAX([Percentage of All Careers]) PCTCareers,
Max(SegmentID) SegmentId,
MAX(DisplayOrder) DisplayOrder,
CASE When Country IS null AND [State] IS null THEN 99 When [STATE] IS null THEN 98 ELSE 0 END CountryType,
ROW_NUMBER() OVER(Partition BY Country,[STATE] ORDER BY CASE When Country IS null THEN 1 ELSE 0 END ,Country,STATE) RowNumber,
RANK() OVER(Partition by Country ORDER BY CASE When Country IS null THEN 99 ELSE 0 END ,Country,STATE) RankedbyState
FROM #tblPopulation
GROUP BY Country,[State],City WITH ROLLUP
) as A
The result set was:
CountrySTATECityPopulation (in Millions)# Programmers (in hundreds)PCTCareersSegmentIdDisplayOrderCountryTypeRowNumberRankedByState
IndiaDelhiWest Delhi71811.00251042
IndiaDelhiNorth Delhi51530.001102022
IndiaDelhiEast Delhi91215.25153012
IndiaDelhiNULL295330.002004052
IndiaDelhiSouth Delhi8812.202004032
IndiaKarnatakaManipal11931.301509037
IndiaKarnatakaBelur22321.108210027
IndiaKarnatakaBangalore92318.099511017
IndiaKarnatakaNULL126531.3015011047
IndiaMaharastraPune202545.1011050411
IndiaMaharastraMumbai302425.102260111
IndiaMaharastraNagpur111219.003970211
IndiaMaharastraNashik61829.107280311
IndiaMaharastraNULL677945.1011080511
IndiaNULLNULL10819745.10200119811
NULLNULLNULL10819745.10200119911
Tom
September 30, 2012 at 2:31 pm
Did you not read my first post????
As I said, this is coming from a View.
The table is to simplify the question. So no PK and all the Joins were already done to get the View.
As to why the syntax, because the sample I took this from and changed for situation used that syntax.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply