Sub-Totalling in SQL - Help.

[milton747]

I'm not a SQL guru.
I need to have a column which shows
order total for each customer (LL_ORDERTOTAL summed foreach customer) and order total for each country (LL_ORDERTOTAL summed foreach country) I think I need some subqueries, but like I say, it's bit beyond me.
Any assistance welcomed.

(The graphing tool I'm using claims it would handle grouping but it seems buggy.)



SELECT
CUSTOMERS.COUNTRY,
CUSTOMERS.CUSTOMERNAME,
ORDERS.ORDERNUMBER,
ORDERDETAILS.QUANTITYORDERED * ORDERDETAILS.PRICEEACH AS LINETOTAL,
(SELECT
SUM( ORDERDETAILS.QUANTITYORDERED * ORDERDETAILS.PRICEEACH)
FROM ORDERDETAILS
WHERE customers.customernumber = orders.customernumber
AND orders.ordernumber = orderdetails.ordernumber) AS LL_ORDERTOTAL
from
CUSTOMERS
inner join orders
on customers.customernumber = orders.customernumber
inner join orderdetails
on orders.ordernumber=orderdetails.ordernumber
order by
CUSTOMERS.COUNTRY,
CUSTOMERS.CUSTOMERNAME,
ORDERS.ORDERNUMBER


- eom -

 

[bborissov]

SELECT CUSTOMERS.COUNTRY,
       CUSTOMERS.CUSTOMERNAME,
       ORDERS.ORDERNUMBER,
       ORDERDETAILS.QUANTITYORDERED * ORDERDETAILS.PRICEEACH AS LINETOTAL,
       CustTotal.Total    AS LL_ORDERTOTAL,
       CountryTotal.Total AS LL_COUNTRYTOTAL
from CUSTOMERS
inner join orders 
      on customers.customernumber = orders.customernumber
inner join orderdetails
      on orders.ordernumber=orderdetails.ordernumber
INNER JOIN (SELECT ORDERDETAILS.customernumber 
                   SUM(ORDERDETAILS.QUANTITYORDERED * 
                       ORDERDETAILS.PRICEEACH) AS Total
            FROM ORDERDETAILS 
            GROUP BY orderdetails.customernumber) CustTotal 
      ON orders.ordernumber = CustTotal.ordernumber        

INNER JOIN (SELECT CUSTOMERS.COUNTRY 
                   SUM(ORDERDETAILS.QUANTITYORDERED * 
                       ORDERDETAILS.PRICEEACH) AS Total
            FROM ORDERDETAILS 
            INNER JOIN Customers
                  ON customers.customernumber = ORDERDETAILS.customernumber
            GROUP BY CUSTOMERS.COUNTRY) CountryTotal 
      ON CUSTOMERS.COUNTRY = CountryTotal.COUNTRY
order by CUSTOMERS.COUNTRY,
         CUSTOMERS.CUSTOMERNAME,
         ORDERS.ORDERNUMBER

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[milton747]

Hi there,

It doesn't likeL

CustTotal.Total AS ....
CountryTotal.Total AS ....


Maybe .Total is not supported in this tool.
Would it possible to use SUM() here?

 

[bborissov]

I doesn't like what?
What is the error?
Derived tables are fully supported in SQL server.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.