Problems with Group By Query

[AdrianG]

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


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.


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.

[Old Pedant]

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

[Old Pedant]

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

[AdrianG]

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



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

[Old Pedant]

Oh...you aren't using MySQL, are you?

You wrote:

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.

[Old Pedant]

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.

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

[guelphdad]

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.