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

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

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

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

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.

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