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: 200
        
mysql
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