SQL Server Performance

a stupid question, but the solution escapes me

Discussion in 'Getting Started' started by londo58, Jun 5, 2007.

  1. londo58 New Member

    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
  2. FrankKalis Moderator

    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>
  3. alzdba Member

    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

Share This Page