| Problems with Group By Query | |
|
Author | Message |
---|
Verse Premium
Posts : 42 Join date : 2011-04-17
| Subject: Problems with Group By Query Wed May 11, 2011 11:11 pm | |
| 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
|
|
| |
False Member
Posts : 18 Join date : 2011-03-09
| Subject: Re: Problems with Group By Query Wed May 11, 2011 11:12 pm | |
| - 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
|
|
| |
False Member
Posts : 18 Join date : 2011-03-09
| Subject: Re: Problems with Group By Query Wed May 11, 2011 11:13 pm | |
| 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... |
|
| |
Verse Premium
Posts : 42 Join date : 2011-04-17
| Subject: Re: Problems with Group By Query Wed May 11, 2011 11:13 pm | |
| 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
|
|
| |
False Member
Posts : 18 Join date : 2011-03-09
| Subject: Re: Problems with Group By Query Wed May 11, 2011 11:14 pm | |
| 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. |
|
| |
False Member
Posts : 18 Join date : 2011-03-09
| Subject: Re: Problems with Group By Query Wed May 11, 2011 11:15 pm | |
| 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
|
|
| |
Sponsored content
| Subject: Re: Problems with Group By Query | |
| |
|
| |
| Problems with Group By Query | |
|