a stupid question, but the solution escapes me | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

a stupid question, but the solution escapes me

I am trying to do something simple – but it is not obvious to me on how to do the following: I have a table with 3 columns that I need to use to create a report with.
productid, qtysold, delivdate I need to provide a report that looks like this: PRODUCTID SOLD SHIPPED all on one line select PRODUCTID, SUM(qtysold) as SHIPPED
from custitem
where delivdate is not NULLand PRODUCTID like ‘17%’
GROUP BY PRODUCTID
ORDER BY PRODUCTID select PRODUCTID, SUM(qtysold) AS SOLD
from custitem
where PRODUCTID like ‘17%’
GROUP BY PRODUCTID
ORDER BY PRODUCTID The above 2 queries: result 1 = PRODUCTID SHIPPED = 22 products shipped result 2 = PRODUCTID SOLD = 1032 products sold I have tried several different functions to try to combine these into one line PRODUCTID SOLD SHIPPED I am sure this is something simple but it escapes me!!! HELP

Something like this should work:<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT t1.PRODUCTID, SUM(t1.qtysold) AS SOLD, x.SHIPPED AS SHIPPED<br /> FROM custitem t1<br /> JOIN (SELECT PRODUCTID, SUM(qtysold) as SHIPPED<br /> FROM custitem<br /> WHERE delivdate IS NOT NULL<br /> GROUP BY PRODUCTID) x<br /> ON t1.PRODUCTID = x.PRODUCTID<br /> WHERE PRODUCTID like ‘17%’ <br /> GROUP BY PRODUCTID<br /> ORDER BY PRODUCTID<br /></font id="code"></pre id="code"><br />untested…[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
Maybe this is simple enough select PRODUCTID, SUM(qtysold) AS SOLD , sum (case when delivdate is NULL then 0 else qtysold end ) as SHIPPED from custitem where PRODUCTID like ‘17%’ GROUP BY PRODUCTID ORDER BY PRODUCTID
]]>