CODEWITHSUNDEEP
sqlsql-server
eqinna
eqinna

Reputation: 331

Total sales amount for customers in cities

I have the following SQL statement that shows the total sales amount for customers in cities who have done at least 2 orders. But let say I only want to show the the city/cities where someone has done at least 2 orders, and that there are two customers living in this city/cities, so what I want to do is to pick out the city/cities where a customer has made at least 2 orders, but then show the total sales amount for both the customers living in this city, even if the other customer has only made one order, how is that done, should there be a comparison statement for the COUNT-operation to be able to show the sales amount for all the customers in the cities, if so - how is it stated?

SELECT c.CityName, SUM(p.Price * o2.Orderquantity) AS 'TotalSalesAmount'
FROM Customers c, Order1 o1, Orderrader o2, Products p,
(SELECT o1.CustomerNr 
FROM Order1 o1
GROUP BY o1.CustomerNr 
HAVING COUNT(o1.CustomerNr) >= 2)
AS a WHERE c.CustomerNr = a.CustomerNr AND c.CustomerNr = o1.CustomerNr
AND o1.Ordernr = o2.Ordernr AND o2.Productnr = p.Productnr
GROUP BY c.CityName

The structures of the tables I work with look like:

'Customers' has the columns: CustomerNr, City Name
'Order1' has columns: Ordernr, CustomerNr
'Order2' has columns: Ordernr, Productnr, Order quantity
'Products' has columns: ProductNr, Price

Sample data: Sample data for the table 'Customers':

- CustomerNr  CityName:
- 01109       New York
- 01999       Los Angeles
- 20090       New York

Sample data for 'Order1':

- Ordernr  CustomerNr
- 1306     01109
- 1307     01109
- 1308     20090

Sample data for 'Order2':

- OrderNr   ProductNr  Order quantity:
- 1306      15-116     3
- 1306      46-701     2
- 1307      15-96      1
- 1308      17-91      1

(etc...)

Sample data for 'Products':

- ProductNr  Price:
- 15-116     44.00
- 15-96      28.50
- 46-701     3000.00
- 17-91      200.00

etc...

According to the SQL statement and sample data above, the desired result I want is:

- CityName  TotalSalesAmount:
- New York   6360.50

Upvotes: 0

Views: 2995

Answers (5)

Shivangi Tak
Shivangi Tak

Reputation: 1

select ci.city_name,pr.product_name,round(sum(ii.line_total_price),2) as tot 
from city ci ,customer cu,invoice i,invoice_item ii,product pr where ci.id=cu.city_id and cu.id=i.customer_id and i.id=ii.invoice_id and ii.product_id=pr.id
group by ci.city_name,pr.product_name
order by tot desc,ci.city_name, pr.product_name

Upvotes: 0

G B
G B

Reputation: 1462

I have interpretted your question as " I need the sum of ALL sales by city, for any city where a single customer has placed more than one order."

select 
    c.City,
    SUM(p.Price * o2.Orderquantity) AS 'TotalSalesAmount' 
from
    (select c.City
    from @t_Customers c
    inner join @t_Order1 o1
        on o1.CustomerNr = c.CustomerNr
    group by c.City
    having count(c.City) > 1) as ct
    inner join @t_Customers c
        on c.City = ct.City
    inner join @t_Order1 o1
        on o1.CustomerNr = c.CustomerNr
    inner join @t_Order2 o2
        on o2.OrderNr = o1.Ordernr
    inner join @t_Products p
        on p.ProductNr = o2.ProductNr
group by c.City

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93734

Try this.

SELECT c.CityName,
       Sum(o2.Orderquantity * p.Price) Total_sale
FROM   Customers C
       JOIN (SELECT o.CustomerNr
             FROM   Order1 o
             GROUP  BY o.CustomerNr
             HAVING Count(o.CustomerNr) >= 2) su
         ON c.CustomerNr = su.CustomerNr
       JOIN Customers c1
         ON c1.CityName = c.CityName
       JOIN Order1 o1
         ON o1.CustomerNr = c1.CustomerNr
       JOIN Order2 o2
         ON o2.Ordernr = o1.Ordernr
       JOIN Products P
         ON o2.ProductNr = P.ProductNr
GROUP  BY c.CityName

Upvotes: 0

su8898
su8898

Reputation: 1713

Try using a subquery. I know this looks a bit nasty but must be working.

Turns out that you have a duplicate Ordernr (1306) for the same CustomerNr in your Order1 example. I am assuming that this might not be the case in your real data.

SELECT c.CityName, 
(Select SUM(order2.quantity * products.Price) from order1 
INNER JOIN Customers On Customers.CustomerNr=order1.CustomerNr 
INNER JOIN Order2 ON Order2.Ordernr=Order1.Ordernr 
INNER JOIN Products ON Products.ProductNr=Order2.ProductNr 
WHERE Customers.CityName=c.CityName) AS 'TotalSalesAmount'
FROM Order1 o1
INNER JOIN (SELECT o1.CustomerNr 
        FROM Order1 o1 
        GROUP BY o1.CustomerNr 
        HAVING COUNT(o1.CustomerNr) >= 2
       ) AS a ON o1.CustomerNr = a.CustomerNr 
INNER JOIN Order2 o2 ON o1.Ordernr = o2.Ordernr 
INNER JOIN Customers c ON o1.CustomerNr = c.CustomerNr
INNER JOIN Products P ON o2.ProductNr = P.ProductNr
GROUP BY c.CityName;

Upvotes: 1

HKImpact
HKImpact

Reputation: 620

Give this a shot.

SELECT CityName, SUM(quantity * Price) AS TotalSalesAmount
FROM (
    SELECT c.CityName, p.ProductNr, b.quantity, p.Price, (b.quantity * p.Price) as total --, SUM(b.quantity * p.Price) AS TotalSalesAmount
    FROM customers c
        INNER JOIN order1 a ON c.CustomerNr = a.CustomerNr
        INNER JOIN order2 b ON a.Ordernr = b.Ordernr
        INNER JOIN products p ON b.Productnr = p.ProductNr
    GROUP BY c.CityName, p.ProductNr, b.quantity, p.Price
    ) AS cust
GROUP BY CityName
HAVING COUNT(cityname) >= 2

Upvotes: 0

Related Questions