WITH ROLLUP problem - 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 2000 Forums

Transact-SQL (2000)

WITH ROLLUP problem

Author

Topic

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2005-06-10 : 08:30:14

Hi,

I need to use the 'with rollup' in my sql statement. However i keep getting dupe rows and can not see how to structure the statement to stop this.

In the first statement below (simple example for northwind), the statement works fine. But in the second statement below, where i add in another field (which i must put in the Group By) - 'employeeid' - the statement dupes rows.

I need to get the results as the first statement but with the extra field in the statement.

All help greatly appreciated.

SELECT o.customerid, od.orderid, SUM(od.quantity*od.unitprice) AS price
FROM Orders o, [Order Details] od
WHERE Year(o.orderdate) = 1996 AND od.orderid=o.orderid
GROUP BY o.customerid, od.orderid with rollup
ORDER BY o.customerid

SELECT o.customerid, od.orderid, EMPLOYEEID, SUM(od.quantity*od.unitprice) AS price
FROM Orders o, [Order Details] od
WHERE Year(o.orderdate) = 1996 AND od.orderid=o.orderid
GROUP BY o.customerid, od.orderid, EMPLOYEEID with rollup
ORDER BY o.customerid

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-10 : 09:12:35

[code]
SELECT o.customerid, od.orderid, (Select EMPLOYEEID from orders where orderid=od.orderid) as EmployeeId
,SUM(od.quantity*od.unitprice) AS price
FROM Orders o, [Order Details] od
WHERE Year(o.orderdate) = 1996 AND od.orderid=o.orderid
GROUP BY o.customerid, od.orderid with rollup
ORDER BY o.customerid
[/code]

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-10 : 10:45:41

You just need to remove the rows where EMPLOYEEID is null and orderid is not null


SELECT
	o.customerid,
	od.orderid,
	EMPLOYEEID,
	SUM(od.quantity*od.unitprice) AS price 
FROM Orders o, [Order Details] od
WHERE Year(o.orderdate) = 1996 AND od.orderid=o.orderid
GROUP BY o.customerid, od.orderid, EMPLOYEEID with rollup
having
	case
	WHEN EMPLOYEEID IS NULL AND
		od.orderid IS NOT NULL
	THEN 0
	ELSE 1
	END = 1
ORDER BY
	o.customerid

CODO ERGO SUM

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2005-06-10 : 12:43:54

cheers for all help...the 'case' worked well in my scenerio.

thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources