none
Query and sum multiple where clauses on a single column

    질문

  • Hi Everyone,

    This is my first post to the community,  I'm stuck on a problem and I was hoping someone can offer some insight.  I am trying to query multiple where clauses on a single column within a single table.  I am trying to get the below result from the below data example. So far I've written the below 2 queries but I cant seem to get it to work.  I've only been doing transact-SQL for a few months and still learning so I would really appreciate some insight! :) 

    P.S. 

    The first query will give me good data for month "Jan2018" but when I add the second select statement for month "Feb2018" on the same database column the quantity drastically increases for both months "Jan2018" and "Feb2018".

     USE [databasename]

    select A.station, A.item, SUM(ABS((A.quantity + A.burnqty * A.packqty))) as Jan2018

    from (select Station, item, Quantity,BurnQty,PackQty from mytran where Month (mydate) = '01' AND Year (mydate) = '2018' AND type IN ( 'i', 'b' ))A
              group by A.station, A.item
              order by a.Station

    USE [databasename]

     select A.station, A.item, SUM(ABS((a.quantity + a.burnqty * a.packqty))) as Jan2018,
                                        SUM(ABS((b.Quantity + b.BurnQty * b.PackQty))) as Feb2018
    from (select Station, item, Quantity, BurnQty,PackQty from mytran where Month (mydate) = '01' AND Year (mydate) = '2018' AND type IN ( 'i', 'b' ))A,
    (select Quantity,BurnQty,PackQty from mytran where Month (mydate) = '02' AND Year (mydate) = '2018' AND type IN ( 'i', 'b' ))B
    group by A.station, A.item
    order by A.Station, A.Item
            

    mydate station item quantity burn qty pack qty type
    01/01/2018    01 1234  5 0 1 i
    01/02/2018    02 1234  2 0 1 i
    01/03/2018    01 1234  7 0 1 b
    01/04/2018    01 1234  1 0 1 i
    01/05/2018    01 1234  3 0 1 b
    02/01/2018    01 1234  7 0 1 b
    02/02/2018    02 1234  1 0 1 b
    02/03/2018    02 1234   3 0 1 b
    02/04/2018    01 1234  7 0 1 b
    02/05/2018   02 1234  3 0 1 b

    Results Messages
    station item Jan2018     Feb2018
    1 1234 16     14
    2 1234 2       7


    2018년 7월 14일 토요일 오전 3:23

답변

  • what you're doing is a cross join so it will cause the query to undergo cartesian product 

    I think what you need is something like this

    select A.station, A.item, 
    SUM(CASE WHEN Month (mydate) = 1 THEN ABS((a.quantity + a.burnqty * a.packqty)) ELSE 0 END) as Jan2018,
    SUM(CASE WHEN Month (mydate) = 1=2 THEN ABS((a.quantity + a.burnqty * a.packqty)) ELSE 0 END) as Feb2018 from (select Station, item, Quantity, BurnQty,PackQty from mytran where Year (mydate) = '2018' AND type IN ( 'i', 'b' ) )A group by A.station, A.item order by A.Station, A.Item


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • 편집됨 Visakh16MVP 2018년 7월 14일 토요일 오전 5:30
    • 답변으로 표시됨 sql_guy_1982 2018년 7월 14일 토요일 오전 5:48
    2018년 7월 14일 토요일 오전 5:30

모든 응답

  • what you're doing is a cross join so it will cause the query to undergo cartesian product 

    I think what you need is something like this

    select A.station, A.item, 
    SUM(CASE WHEN Month (mydate) = 1 THEN ABS((a.quantity + a.burnqty * a.packqty)) ELSE 0 END) as Jan2018,
    SUM(CASE WHEN Month (mydate) = 1=2 THEN ABS((a.quantity + a.burnqty * a.packqty)) ELSE 0 END) as Feb2018 from (select Station, item, Quantity, BurnQty,PackQty from mytran where Year (mydate) = '2018' AND type IN ( 'i', 'b' ) )A group by A.station, A.item order by A.Station, A.Item


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • 편집됨 Visakh16MVP 2018년 7월 14일 토요일 오전 5:30
    • 답변으로 표시됨 sql_guy_1982 2018년 7월 14일 토요일 오전 5:48
    2018년 7월 14일 토요일 오전 5:30
  • I think you want

    Create Table mytran(mydate datetime, station char(2), item int, quantity int, burnqty int, packqty int, type char(1));
    Insert mytran(mydate, station, item, quantity, burnqty, packqty, type) Values
    ('01/01/2018', 	   '01', 	1234, 	 5, 	0, 	1, 	'i'),
    ('01/02/2018', 	   '02', 	1234, 	 2, 	0, 	1, 	'i'),
    ('01/03/2018', 	   '01', 	1234, 	 7, 	0, 	1, 	'b'),
    ('01/04/2018', 	   '01', 	1234, 	 1, 	0, 	1, 	'i'),
    ('01/05/2018', 	   '01', 	1234, 	 3, 	0, 	1, 	'b'),
    ('02/01/2018', 	   '01', 	1234, 	 7, 	0, 	1, 	'b'),
    ('02/02/2018', 	   '02', 	1234, 	 1, 	0, 	1, 	'b'),
    ('02/03/2018', 	   '02', 	1234,  	 3, 	0, 	1, 	'b'),
    ('02/04/2018', 	   '01', 	1234, 	 7, 	0, 	1, 	'b'),
    ('02/05/2018', 	   '02', 	1234, 	 3, 	0, 	1, 	'b');
    
    select m.station, m.item,
       SUM(ABS(Case When Year(m.mydate) = 2018 And Month(m.mydate) = 1 Then m.quantity + m.burnqty * m.packqty Else 0 End)) as Jan2018,
       SUM(ABS(Case When Year(m.mydate) = 2018 And Month(m.mydate) = 2 Then m.quantity + m.burnqty * m.packqty Else 0 End)) as Feb2018
    from mytran m
    where m.mydate >= '20180101' And m.mydate < '20180301'
    And type In('i', 'b')
    group by m.station, m.item
    order by Station;
    
    Tom

    2018년 7월 14일 토요일 오전 5:37
  • thank you so much!!!  I had to make a few little tweaks but this definitely was the answer I was looking for..thanks again!

    P.S

    The below query is my final result.

    use [databasename]

    select A.station, A.item,
    SUM(CASE WHEN Month (a.mydate) = 1 THEN ABS((a.quantity + a.burnqty * a.packqty)) ELSE 0 END) as Jan2018,
    SUM(CASE WHEN Month (a.mydate) = 2 THEN ABS((a.quantity + a.burnqty * a.packqty)) ELSE 0 END) as Feb2018
    from (select mydate,Station, item, Quantity, BurnQty,PackQty from mytran
    where  Year (mydate) = '2018'
    AND type IN ( 'i', 'b' )
    )A
    group by A.station, A.item
    order by A.Station, A.Item

    2018년 7월 14일 토요일 오전 5:51
  • Tom,

    I really like this one too as it gives me more flexibility to choose certain month\years for my alias.  Thank you so much you guys are amazing!

    2018년 7월 14일 토요일 오전 6:08