Get only ROLLUP? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get only ROLLUP?

Author

Topic

ceestand
Starting Member

2 Posts

Posted - 2008-08-28 : 10:41:20

Is there a way to return only the last row of a result set, the one generated by "WITH ROLLUP"?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 10:44:05

That you can do at your front end. just filter for ones having NULL values for grouped fields.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 11:12:39

Yes.

SELECT		Type,
		Number,
		COUNT(*) AS Items
FROM		master..spt_values
GROUP BY	Type,
		Number
WITH ROLLUP
--HAVING		Type IS NULL
--HAVING		Number IS NULL

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-28 : 11:26:46

Don't understand why do you want to do that ?

Is it the same without the group by and other columns in the SELECT ?


SELECT		Type,
		Number,
		COUNT(*) AS Items
FROM		master..spt_values
GROUP BY	Type,
		Number
WITH ROLLUP

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-28 : 11:34:24

Isn't this the same ?

DECLARE @sample TABLE
(
	col1	int,
	col2	int,
	col3	int,
	col4	int
)

INSERT INTO @sample
SELECT	1, 1, 1, 1	UNION ALL
SELECT	1, 1, 1, 2	UNION ALL
SELECT	1, 1, 1, 3	UNION ALL
SELECT	1, 1, 2, 1	UNION ALL
SELECT	1, 1, 2, 2	UNION ALL
SELECT	2, 2, 1, 1	UNION ALL
SELECT	2, 2, 1, 2

-- ROLL UP
SELECT	col1, col2, SUM(col3), SUM(col4)
FROM	@sample
GROUP BY col1, col2
WITH rollup

/*
col1        col2                                
----------- ----------- ----------- ----------- 
          1           1           7           9 
          1 NULL                  7           9 
          2           2           2           3 
          2 NULL                  2           3 
NULL        NULL                  9          12 

(5 row(s) affected)
*/

-- SUM
SELECT	SUM(col3), SUM(col4)
FROM	@sample
/*
----------- ----------- 
          9          12 
*/

Maybe i misunderstood you. Can you explain with sample data & expected result ?

KH
[spoiler]Time is always against us[/spoiler]

ceestand
Starting Member

2 Posts

Posted - 2008-08-28 : 11:54:00

Well, here's the reason; I'm trying to summarize the number of, and dollar amount of orders placed over the last year, grouped by hour. So for each hour of each day, I'd get a total. Tables are as such:

Order
------
OrderID
OrderTime

OrderDetail
-----------
OrderDetailID
OrderID
Price

I can easily get the sum of the OrderDetail items for each order, but I'm having trouble then summing the total of the orders for each hour. My plan was to get the rollup and insert it into a temp table, but based on some shorter amounts of time, I'd estimate it to take ~2 hours to complete.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 11:57:42

[code]SELECT DATEADD(HOUR, DATEDIFF(HOUR, '19000101', o.OrderTime), '19000101') AS theHour,
SUM(od.Price) AS thePrice
FROM Order AS o
INNER JOIN OrderDetail AS od ON od.OrderID = o.OrderID
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, '19000101', o.OrderTime), '19000101')[/code]Also make sure you have proper indexes in the tables.

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-28 : 11:57:55

can you post some sample data and the required result ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-28 : 11:59:57

[code]SELECT DATEPART(HOUR, o.OrderTime) AS theHour,
COUNT(DISTINCT o.OrderID) AS theOrder
SUM(od.Price) AS thePrice
FROM Order AS o
INNER JOIN OrderDetail AS od ON od.OrderID = o.OrderID
GROUP BY DATEPART(HOUR, o.OrderTime)[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources