Need to combine results in SQL query

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

Need to combine results in SQL query

Author

Topic

Zoomer36
Starting Member

13 Posts

Posted - 2012-06-26 : 13:20:37

Hello,

I need to combine data in a SQL query. I have it very close but need some help. I need to get a report of all the invoices for a year. On each invoice, I need to sum the number or cartons sold so I can convert that to the number of packs sold. Problem is that we have two types of cartons. A CARTON of 10 and a 5/20-PK of 5.
I do a CASE statement that SALE_MEAS = 'CARTON' then SUM(QTY_SHIPPED * 10) else SUM(QTY_SHIPPED * 5) END as PACKS, it gives me 2 invoice entries for the different types of cartons. I need to add those two together so there is only one entry for each invoice.

This is part of a larger report and I need to cut and paste the carton/pack numbers into each invoice.

Thanks in advance for any and all help!

Charlie
This is my script:

SELECT
CASE WHEN I.STATUS = 9 THEN 'Invoice' ELSE 'Return' END AS DOCTYPE,
I.ORDER_NO, I.ORDER_DATE,
CASE WHEN I.STATUS = 9 THEN SUM(X.QTY_SHIP) ELSE SUM(X.QTY_SHIP * -1) END AS QTY_SHIPPED,
CASE WHEN IT.SALE_MEAS = 'CARTON' THEN (SUM(X.QTY_SHIP) * 10) ELSE (SUM(X.QTY_SHIP) * 5) END AS PACKS
FROM INVOICES I WITH (NOLOCK)
INNER JOIN X_INVOIC X WITH (NOLOCK) ON I.DOC_NO = X.ORDER_NO AND I.STATUS = X.STATUS
INNER JOIN ITEMS IT WITH (NOLOCK) ON X.ITEM_CODE = IT.ITEMNO
WHERE I.STATUS IN (9,12)
AND IT.CATEGORY IN ('CIGS','CIGSSPL','CIG25')
AND (I.ORDER_DATE >= '01/01/2008') AND (I.ORDER_DATE < '1/01/2009')
GROUP BY I.ORDER_DATE, I.STATUS, I.ORDER_NO, IT.SALE_MEAS
ORDER BY I.ORDER_DATE, I.ORDER_NO

Have a great day!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-26 : 14:18:10

SELECT
CASE WHEN I.STATUS = 9 THEN 'Invoice' ELSE 'Return' END AS DOCTYPE,
I.ORDER_NO, I.ORDER_DATE,
CASE WHEN I.STATUS = 9 THEN SUM(X.QTY_SHIP) ELSE SUM(X.QTY_SHIP * -1) END AS QTY_SHIPPED,

SUM(CASE WHEN IT.SALE_MEAS = 'CARTON' THEN X.QTY_SHIP* 10 ELSE X.QTY_SHIP * 5 END
AS PACKS
FROM INVOICES I WITH (NOLOCK)
INNER JOIN X_INVOIC X WITH (NOLOCK) ON I.DOC_NO = X.ORDER_NO AND I.STATUS = X.STATUS
INNER JOIN ITEMS IT WITH (NOLOCK) ON X.ITEM_CODE = IT.ITEMNO
WHERE I.STATUS IN (9,12)
AND IT.CATEGORY IN ('CIGS','CIGSSPL','CIG25')
AND (I.ORDER_DATE >= '01/01/2008') AND (I.ORDER_DATE < '1/01/2009')
GROUP BY I.ORDER_DATE, I.STATUS, I.ORDER_NO, ~~IT.SALE_MEAS~~
ORDER BY I.ORDER_DATE, I.ORDER_NO

Jim

Everyday I learn something that somebody else already knew

Zoomer36
Starting Member

13 Posts

Posted - 2012-06-26 : 14:31:48

Hi Jim,

Thank you but that didn't work. I got the message:

Server: Msg 195, Level 15, State 10, Line 7
'SUM' is not a recognized function name.

I am using SQL Server 2000.

Have a great day!

Zoomer36
Starting Member

13 Posts

Posted - 2012-06-27 : 16:38:43

Hi Jim,

You are a genius! Thank you. I have been playing with it for the past 2 days. I just now finally realized that I had left the SUM function inside the SUM. Of course that wasn't going to work.

Thanks for your help! This one is solved and I can't thank you enough.

Have a great day!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources