Confusing alias mySQL

Posted by Taylor on Stack Overflow See other posts from Stack Overflow or by Taylor
Published on 2012-09-08T09:13:57Z Indexed on 2012/09/08 9:38 UTC
Read the original article Hit count: 132

Filed under:

I keep getting the same number outputted for the Total Sales, Minimum Sale, Largest Sale and Average Sale. The Total Invoices is working perfectly, but I cant seem to figure out how to fix the other ones.

Here's the query:

SELECT SUM( b.`Number of Invoices`) AS `Total Invoices`, 
       SUM( b.`Total Customer Purchases`) AS `Total Sales`, 
       MIN( b.`Total Customer Purchases`) AS `Minimum Sale`, 
       MAX( b.`Total Customer Purchases`) AS `Largest Sale`, 
       AVG( b.`Total Customer Purchases`) AS `Average Sale`
FROM (SELECT a.CUS_CODE, 
             COUNT(a.`Number of Invoices`) AS `Number of Invoices`,
             SUM(a.`Invoice Total`) AS `Total Customer Purchases`
      FROM ( SELECT CUS_CODE,
                    LINE.INV_NUMBER AS `Number of Invoices`,
                    SUM(LINE.LINE_UNITS * LINE.LINE_PRICE) AS `Invoice Total`
             FROM `ttriggs`.`INVOICE`, `ttriggs`.`LINE`
             WHERE INVOICE.INV_NUMBER = LINE.INV_NUMBER
             GROUP BY CUS_CODE, LINE.INV_NUMBER 
           ) a 
     ) b
GROUP BY b.CUS_CODE;

Heres the database diagram https://www.dropbox.com/s/b8cy5l29jwh8lyv/1_edit.jpg

Subquery generates:

CUS_CODE  10011     
Number of Invoices  8  
Total Customer Purchases  1119.03

Any help is greatly appreciated, Thanks!

© Stack Overflow or respective owner

Related posts about mysql