Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Apr 2011
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Problems with Group By Query

    Hope you don't mind. It's not a MySQL problem, but it is an SQL problem that I'm having difficulty with.

    Code:
    SELECT     LEFT(ITEM, 10) AS Expr1, SUM(QUANTS) AS Returns
    FROM         ITEMS
    WHERE      ITEM_STATE = 'RT'
    GROUP BY LEFT(ITEM, 10)
    ORDER BY Returns DESC
    The above query works and shows the products that have been returned.
    However to make any sense of this query I'd also like to add two additional columns to this above query.

    The extra columns would be

    1. Items shipped
    2. A percentage showing Items Returned/Items Shipped.

    Therefore we can see if we are having return problems with particular items, where the % of returns are high.

    The problem is that items shipped uses the same columns as items returned so I'm not sure how to combine this on the same query.
    As an example if I wanted just to see items shipped i would use the following query.

    Code:
    SELECT     LEFT(ITEM, 10) AS Expr1, SUM(QUANTS) AS Shipped
    FROM         ITEMS
    WHERE     ITEM_STATE = 'SH'
    GROUP BY LEFT(ITEM, 10)
    ORDER BY Shipped DESC
    Any help is much appreciated!

    Thanks.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    Code:
    SELECT  LEFT(ITEM, 10) AS Expr1, 
           SUM( IF( ITEM_STATE = 'SH', QUANTS, 0 ) ) AS Shipped,
           SUM( IF( ITEM_STATE = 'RT', QUANTS, 0 ) ) AS Returned,
           100.0 * SUM( IF( ITEM_STATE = 'RT', QUANTS, 0 ) ) / SUM( IF( ITEM_STATE = 'SH', QUANTS, 0 ) ) AS PercentReturned
    FROM         ITEMS
    GROUP BY LEFT(ITEM, 10)
    ORDER BY PercentReturned DESC
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    AdrianG (04-15-2011)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    CAUTION: If any of your items have a SHIPPED quantity of zero, the division by zero in computing the percentreturned will blow up.

    I don't imagine that can happen, but...
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New to the CF scene
    Join Date
    Apr 2011
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for that. For some reason the If statement wasn't working, but your suggestion gave me the idea to change it to a case statement, and it worked great!
    so I just changed it to

    Code:
    SUM(CASE WHEN ITEM_STATE = 'RT' THEN QUANTS ELSE NULL END) AS Returns, 
                          SUM(CASE WHEN ITEM_STATE = 'SH' THEN QUANTS ELSE NULL END) AS shipped

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    Oh...you aren't using MySQL, are you?

    You wrote:
    Code:
    It's not a MySQL problem, but it is an SQL problem
    SQL is juat a language, so I just assumed you meant you were having problems with the language part, not the PHP/MySQL interface or the like.

    You meant SQL Server, right? Yes, SQL Server doesn't have IF( ) but CASE WHEN works just fine.

    For what it's worth, MySQL has both IF and CASE WHEN. Access has only IF (though in Access it's spelled IIF). And so on.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    Quote Originally Posted by AdrianG View Post
    Code:
    SUM(CASE WHEN ITEM_STATE = 'RT' THEN QUANTS ELSE NULL END) AS Returns, 
                          SUM(CASE WHEN ITEM_STATE = 'SH' THEN QUANTS ELSE NULL END) AS shipped
    It's not really important, but I'd suggest changing the NULL in both of those ELSE conditions to a simple 0 instead.

    Reason: If there are *no* matches on the given ITEM_STATE, then you will end up doing a SUM( ) of all NULLs and the result will be NULL, not zero.
    Code:
        SUM(CASE WHEN ITEM_STATE = 'RT' THEN QUANTS ELSE 0 END) AS Returns, 
        SUM(CASE WHEN ITEM_STATE = 'SH' THEN QUANTS ELSE 0 END) AS shipped
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by AdrianG View Post
    Hope you don't mind. It's not a MySQL problem, but it is an SQL problem that I'm having difficulty with.
    in future please choose the general database forum then and specify your database application in the headline just to make it clear if it is mssql or other rdbms.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •