Can I exclude repetitive rows in this ROLLUP? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Can I exclude repetitive rows in this ROLLUP?

Author

Topic

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2008-08-14 : 14:41:59

A report I'm working on is based on the following SELECT statement; it was good enough at the outset but now I'm being asked to subtotal every part ordered (by part number) and show a grand total.

SELECT
armast.fcinvoice as INVNUM,
CONVERT(CHAR(10), armast.finvdate, 101) AS INVDATE, 
aritem.fpartno AS PARTNO, 
aritem.fshipqty as SHIPQTY, 
aritem.fprice AS UNITPRICE,
SUM(aritem.fprice * aritem.fshipqty) AS INVTOTAL 

FROM
aritem LEFT OUTER JOIN armast ON aritem.fcinvoice=armast.fcinvoice 

WHERE
armast.finvdate BETWEEN ('2007-08-01') AND ('2008-08-13')
AND armast.fcustno in ('082130') 
AND aritem.fpartno in ('RedPart', 'WhitePart', 'GreenPart') 

GROUP BY
aritem.fpartno, aritem.fprice, aritem.fshipqty, armast.fcinvoice, armast.finvdate

(with this result)

[CODE]INVNUM INVDATE PARTNO SHIPQTY UNITPRICE INVTOTAL
------ ------- ------ ------- --------- --------
75000 7/18/2008 RedPart 22000 0.31 6820
70125 3/5/2008 RedPart 5000 0.34 1700
66232 10/23/2007 WhitePart 7000 0.085 595
70125 3/5/2008 GreenPart 5000 0.274 1370[/CODE]

So I did some looking into ROLLUP and thought maybe it could help. It DOES give me the Total Ordered that I wanted, but it also returns a LOT of rows of repetitive data:

SELECT
	armast.fcinvoice AS INVNUM,
	CONVERT(CHAR(10), armast.finvdate, 101) AS INVDATE, 
	CASE
		WHEN (GROUPING(aritem.fpartno) = 1) THEN 'Total Ordered'
		ELSE aritem.fpartno
	END AS PARTNO,
	SUM(aritem.fshipqty) AS SHIPQTY,
	aritem.fprice as UNITPRICE,
	SUM(aritem.fprice * aritem.fshipqty) AS INVTOTAL
	

FROM	
	aritem left outer join armast on aritem.fcinvoice = armast.fcinvoice

WHERE
	armast.finvdate BETWEEN '2007-08-01' AND '2008-08-13'
	AND armast.fcustno in ('082130')
	AND aritem.fpartno in ('RedPart', 'WhitePart', 'GreenPart')

GROUP BY 
	aritem.fpartno, aritem.fprice, armast.finvdate, armast.fcinvoice WITH ROLLUP

(which returns this:)

INVNUM	INVDATE		PARTNO		SHIPQTY		UNITPRICE	INVTOTAL
------	-------		------		-------		---------	--------
75000	7/18/2008	RedPart         22000		0.31		6820
NULL	7/18/2008	RedPart         22000		0.31		6820
NULL	NULL		RedPart         22000		0.31		6820
70125	3/5/2008	RedPart         5000		0.34		1700
NULL	3/5/2008	RedPart         5000		0.34		1700
NULL	NULL		RedPart         5000		0.34		1700
NULL	NULL		RedPart         27000		NULL		8520
66232	10/23/2007	WhitePart       7000		0.085		595
NULL	10/23/2007	WhitePart       7000		0.085		595
NULL	NULL		WhitePart       7000		0.085		595
NULL	NULL		WhitePart       7000		NULL		595
70125	3/5/2008	GreenPart       5000		0.274		1370
NULL	3/5/2008	GreenPart       5000		0.274		1370
NULL	NULL		GreenPart       5000		0.274		1370
NULL	NULL		GreenPart       5000		NULL		1370
NULL	NULL		Total Ordered   39000		NULL		10485

Now, I'm pretty sure I can programmatically discard the useless rows through some combination of DataView and DataGrid tinkering, but before I go that route, thought I'd check here and see if maybe I'm using ROLLUP correctly.

If possible, I want to trim the return to something like this:

INVNUM	INVDATE		PARTNO		SHIPQTY		UNITPRICE	INVTOTAL
------	-------		------		-------		---------	--------
75000	7/18/2008	RedPart         22000		0.31		6820
70125	3/5/2008	RedPart         5000		0.34		1700
NULL	NULL		RedPart         27000		NULL		8520
66232	10/23/2007	WhitePart       7000		0.085		595
NULL	NULL		WhitePart       7000		NULL		595
70125	3/5/2008	GreenPart       5000		0.274		1370
NULL	NULL		GreenPart       5000		NULL		1370
NULL	NULL		Total Ordered   39000		NULL		10485

I was messing around with it earlier doing a CASE statement like the one for aritem.fpartno only on aritem.fprice and I got an error (converting varchar to numeric, even though the field is numeric?) so I suspect I'm doing something just a little wrong here.

Does anyone have any ideas? I'd sure appreciate a shove in the right direction.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 15:07:22

try taking out aritem.fprice from GROUP BY and putting a MAX(aritem.fprice) in select list.

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2008-08-14 : 15:34:53

quote:
Originally posted by visakh16

try taking out aritem.fprice from GROUP BY and putting a MAX(aritem.fprice) in select list.

Thanks for pointing that out! That helped a lot, got it down to this:

INVNUM	INVDATE		PARTNO		SHIPQTY		UNITPRICE	INVTOTAL
------	-------		------		-------		---------	--------
70125	3/5/2008	RedPart               	5000	0.34		1700
NULL	3/5/2008	RedPart               	5000	0.34		1700
75000	7/18/2008	RedPart               	22000	0.31		6820
NULL	7/18/2008	RedPart               	22000	0.31		6820
NULL	NULL		RedPart               	27000	0.34		8520
66232	10/23/2007	WhitePart               7000	0.085		595
NULL	10/23/2007	WhitePart               7000	0.085		595
NULL	NULL		WhitePart               7000	0.085		595
70125	3/5/2008	GreenPart               5000	0.274		1370
NULL	3/5/2008	GreenPart               5000	0.274		1370
NULL	NULL		GreenPart               5000	0.274		1370
NULL	NULL		Total Ordered           39000	0.34		10485

I'll have to deal with the MAX(PRICE) showing up in the total line, but that's no biggie. Any ideas on how to get rid of the 2nd duplicate (where INVNUM is NULL under each part?)

EDIT: Hm, I applied MAX to the Invoice field, and that did the trick but I'll have to tinker with it to see if it plays nice with non-numeric invoices. But, if it does, this is going to work exactly as I need it to.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 15:58:40

Have a look at the GROUPING function.

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

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-14 : 16:07:53

just for the record... ROLLUP in SQL 2008 is much more flexible, you can choose which fields to roll up. but you're not using it of course

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources