Rollup issues

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Rollup issues

Rollup issues

Rate Topic

Display Mode

Topic Options

Author

Message

tshad

Posted Friday, September 28, 2012 6:43 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:12 AM
Points: 99, Visits: 262

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.

Country	State	City	Population (in Millions)
India	Delhi	East Delhi	9
India	Delhi	North Delhi	5
India	Delhi	South Delhi	8
India	Delhi	West Delhi	7
India	Delhi	NULL	29
India	Karnataka	Bangalore	9
India	Karnataka	Belur	2
India	Karnataka	Manipal	1
India	Karnataka	NULL	12
India	Maharastra	Mumbai	30
India	Maharastra	Nagpur	11
India	Maharastra	Nashik	6
India	Maharastra	Pune	20
India	Maharastra	NULL	67
India	NULL	NULL	108
NULL	NULL	NULL	108

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).

SegmentID	Country	State	City	Population (in Millions)
15	India	Delhi	East Delhi	9
15	India	Delhi	NULL	9
25	India	Delhi	West Delhi	7
25	India	Delhi	NULL	7
110	India	Delhi	North Delhi	5
110	India	Delhi	NULL	5
200	India	Delhi	South Delhi	8
200	India	Delhi	NULL	8
NULL	India	Delhi	NULL	29
82	India	Karnataka	Belur	2
82	India	Karnataka	NULL	2
95	India	Karnataka	Bangalore	9
95	India	Karnataka	NULL	9
150	India	Karnataka	Manipal	1
150	India	Karnataka	NULL	1
NULL	India	Karnataka	NULL	12
22	India	Maharastra	Mumbai	30
22	India	Maharastra	NULL	30
39	India	Maharastra	Nagpur	11
39	India	Maharastra	NULL	11
72	India	Maharastra	Nashik	6
72	India	Maharastra	NULL	6
110	India	Maharastra	Pune	20
110	India	Maharastra	NULL	20
NULL	India	Maharastra	NULL	67
NULL	India	NULL	NULL	108
NULL	NULL	NULL	NULL	108

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.

Country	State	City	Population (in Millions)	# Programmers (in hundreds)
India	Delhi	East Delhi	9	12
India	Delhi	NULL	9	12
India	Delhi	West Delhi	7	18
India	Delhi	NULL	7	18
India	Delhi	North Delhi	5	15
India	Delhi	NULL	5	15
India	Delhi	South Delhi	8	8
India	Delhi	NULL	8	8
India	Delhi	NULL	29	53
India	Karnataka	Belur	2	23
India	Karnataka	NULL	2	23
India	Karnataka	Bangalore	9	23
India	Karnataka	NULL	9	23
India	Karnataka	Manipal	1	19
India	Karnataka	NULL	1	19
India	Karnataka	NULL	12	65
India	Maharastra	Mumbai	30	24
India	Maharastra	NULL	30	24
India	Maharastra	Nagpur	11	12
India	Maharastra	NULL	11	12
India	Maharastra	Nashik	6	18
India	Maharastra	NULL	6	18
India	Maharastra	Pune	20	25
India	Maharastra	NULL	20	25
India	Maharastra	NULL	67	79
India	NULL	NULL	108	197
NULL	NULL	NULL	108	197

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

Post #1366142

tshad

Posted Sunday, September 30, 2012 12:25 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:12 AM
Points: 99, Visits: 262

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

Country	State	City	Population (in Millions)	# Programmers (in hundreds)	(No column name)	(No column name)
India	Delhi	East Delhi	9	12	15.25	15
India	Delhi	North Delhi	5	15	30.00	110
India	Delhi	South Delhi	8	8	12.20	200
India	Delhi	West Delhi	7	18	11.00	25
India	Delhi	NULL	29	53	30.00	200
India	Karnataka	Bangalore	9	23	18.09	95
India	Karnataka	Belur	2	23	21.10	82
India	Karnataka	Manipal	1	19	31.30	150
India	Karnataka	NULL	12	65	31.30	150
India	Maharastra	Mumbai	30	24	25.10	22
India	Maharastra	Nagpur	11	12	19.00	39
India	Maharastra	Nashik	6	18	29.10	72
India	Maharastra	Pune	20	25	45.10	110
India	Maharastra	NULL	67	79	45.10	110
India	NULL	NULL	108	197	45.10	200
NULL	NULL	NULL	108	197	45.10	200

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

Post #1366286

CELKO

Posted Sunday, September 30, 2012 2:09 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

Give me a country name that is 100 charters long; good programmers use the CHAR(3) ISO Standard codes. Why not use the base table and not this VUIEW you never show us. You load the query with display headers in violation of C/S. Why did change the scale of measurement from Millions to Hundreds? (would you mix SI and Imperial units in another attribute?) Why are you using the old WITH ROLLUP and not the current ANSI/ISO Standard syntax?

It looks like you only need this:

CREATE TABLE Populations
(country_code CHAR(3) NOT NULL, -- iso-3166
state_code CHAR(15) NOT NULL, -- Postal Union rules
city_name VARCHAR(35) NOT NULL, --Postal Union rules
PRIMARY KEY (country_code, state_code, city_name),
city_population INTEGER NOT NULL
CHECK (city_population > 0),
programmer_population INTEGER NOT NULL
CHECK (programmer_population > 0));

SELECT country_code, state_code, city_name,
SUM (city_population) AS city_population_tot,
SUM (programmer_population) AS programmer_population_tot
FROM Populations
GROUP BY ROLLUP (country_code, state_code, city_name);

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1366293

tshad

Posted Sunday, September 30, 2012 2:13 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:12 AM
Points: 99, Visits: 262

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:

Country	STATE	City	Population (in Millions)	# Programmers (in hundreds)	PCTCareers	SegmentId	DisplayOrder	CountryType	RowNumber	RankedByState
India	Delhi	West Delhi	7	18	11.00	25	1	0	4	2
India	Delhi	North Delhi	5	15	30.00	110	2	0	2	2
India	Delhi	East Delhi	9	12	15.25	15	3	0	1	2
India	Delhi	NULL	29	53	30.00	200	4	0	5	2
India	Delhi	South Delhi	8	8	12.20	200	4	0	3	2
India	Karnataka	Manipal	1	19	31.30	150	9	0	3	7
India	Karnataka	Belur	2	23	21.10	82	10	0	2	7
India	Karnataka	Bangalore	9	23	18.09	95	11	0	1	7
India	Karnataka	NULL	12	65	31.30	150	11	0	4	7
India	Maharastra	Pune	20	25	45.10	110	5	0	4	11
India	Maharastra	Mumbai	30	24	25.10	22	6	0	1	11
India	Maharastra	Nagpur	11	12	19.00	39	7	0	2	11
India	Maharastra	Nashik	6	18	29.10	72	8	0	3	11
India	Maharastra	NULL	67	79	45.10	110	8	0	5	11
India	NULL	NULL	108	197	45.10	200	11	98	1	1
NULL	NULL	NULL	108	197	45.10	200	11	99	1	1

Tom

Post #1366294

tshad

Posted Sunday, September 30, 2012 2:31 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:12 AM
Points: 99, Visits: 262

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.

Post #1366295

« Prev Topic | Next Topic »

Permissions