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
  • 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

    • 답변으로 표시됨 sql_guy_1982 2018년 8월 2일 목요일 오후 4:18
    2018년 7월 14일 토요일 오전 5:37

모든 응답

  • 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

    • 답변으로 표시됨 sql_guy_1982 2018년 8월 2일 목요일 오후 4:18
    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
  • How do I get the same results without having to put in the months and years every time?  Example would be something like


    SUM(ABS(Case When Month -1 Then m.quantity + m.burnqty * m.packqty Else 0 End)) as Jan2018, SUM(ABS(Case When Month -2 Then m.quantity + m.burnqty * m.packqty Else 0 End)) as Feb2018,

    SUM(ABS(Case When Month -3 Then m.quantity + m.burnqty * m.packqty Else 0 End)) as Mar2018

    ect ect...


    sql_guy_1982

    2018년 8월 2일 목요일 오후 4:23
  • How do I get the same results without having to put in the months and years every time?  Example would be something like


    SUM(ABS(Case When Month -1 Then m.quantity + m.burnqty * m.packqty Else 0 End)) as Jan2018, SUM(ABS(Case When Month -2 Then m.quantity + m.burnqty * m.packqty Else 0 End)) as Feb2018,

    SUM(ABS(Case When Month -3 Then m.quantity + m.burnqty * m.packqty Else 0 End)) as Mar2018

    ect ect...


    sql_guy_1982

    you need to use dynamic sql for that

    see

    https://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

    so in your case it will look like this

    DECLARE @Year int = 2018 -- pass any value for year here
    
    DECLARE @Months table
    (
    MonthNo varchar(2),
    MonthName varchar(30)
    )
    INSERT @Months
    SELECT DISTINCT MONTH(mydate),DATENAME(mm,mydate) + DATENAME(yy,mydate)
    FROM mytran
    WHERE Year (mydate) = @Year
    
    DECLARE @PvtCol varchar(max),@Qry varchar(max)
    
    
    SET @PvtCol = STUFF((SELECT ',SUM(CASE WHEN Month (mydate) = '+ MonthNo + ' THEN ABS((a.quantity + a.burnqty * a.packqty)) ELSE 0 END) as ' + MonthName 
    FROM @Months
    FOR XML PATH('')),1,1,'')
    
    SET @Qry = 'select A.station, A.item,' + 
    @PvtCol + '
    from (select Station, item, Quantity, BurnQty,PackQty from mytran 
    where  Year (mydate) = ''' + CAST(@Year as varchar(4)) + '''
    AND type IN ( ''i'', ''b'' )
    )A
    group by A.station, A.item
    order by A.Station, A.Item'
    
    
    EXEC(@Qry)


    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

    2018년 8월 2일 목요일 오후 4:46
  • is there no simpler way?  Why cant I use something like

    Sum(CASE WHEN mt.mydate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)THEN Abs((mt.quantity + mt.burnqty * mt.packqty))

                    ELSE 0 END) AS [Jul-18],

    Sum(CASE WHEN mt.mydate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-2,0)

                    and mt.MyDate < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)THEN Abs((mt.quantity + mt.burnqty * mt.packqty))

                    ELSE 0 END) AS [Jun-18],

    the second query is supposed to only show the 2nd month only but it is summing the first and second.  Do you know how to alter it so it only shows the second month but ignores for first month?


    sql_guy_1982

    2018년 8월 2일 목요일 오후 6:12
  • is there no simpler way?  Why cant I use something like

    Sum(CASE WHEN mt.mydate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)THEN Abs((mt.quantity + mt.burnqty * mt.packqty))

                    ELSE 0 END) AS [Jul-18],

    Sum(CASE WHEN mt.mydate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-2,0)

                    and mt.MyDate < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)THEN Abs((mt.quantity + mt.burnqty * mt.packqty))

                    ELSE 0 END) AS [Jun-18],

    the second query is supposed to only show the 2nd month only but it is summing the first and second.  Do you know how to alter it so it only shows the second month but ignores for first month?


    sql_guy_1982

    This will work only for the current date

    or is it always passed date back 1 month, 2 month etc?

    If yes, you can use logic like below


    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

    2018년 8월 2일 목요일 오후 6:39
  • It is always 12 months, separated by month.  Month one should only show January transactions, month 2 should only show February transactions, not both Jan and Feb together.  Is there a way to do that using DATEADD and DATEDIFF?  I did not see your logic below?

    sql_guy_1982

    2018년 8월 2일 목요일 오후 6:45