SQL total spent per customer

Question

this is my first time asking a question on here, and I can usually find what I need just from seaching, however this time I'm stuck and I'm hoping someone here can help.

Here's the gist. I have created a database for a hair dressing studio. there are five tables. The only one's this question deals with is the Customers table (stores customer details), the ProductSales table (store details about each product sale, and HairCuts table, store details about each cut.

I need a query that will list the total spent on both hair cuts and products for each customer in the customers table.

I have two separate queries that work fine. Each one calculates the total spent by each customer for EITHER hair cuts or products. I need to somehow combine these into one that will show the total.

SELECT c.customer_ID, c.first_Name, c.last_name, 
SUM(hc.cost) AS hc_sales_total
FROM Customers c, HairCuts hc
WHERE c.customer_ID = hc.customer_ID
GROUP BY c.customer_ID;

SELECT c.customer_ID, c.first_Name, c.last_name, 
SUM(ps.cost) AS ps_sales_total
FROM Customers c,ProductSales ps
WHERE c.customer_ID = ps.customer_ID
GROUP BY c.customer_ID;

I believe the issues I am havng are stemming from the fact that, while all customers have purchased at least one hair cut, not all have purchased products. Anyway, any help would be much appreciated.

Solution 1

UNION your two queries together.

Then you'll have a table with all the costs.

From then you can treat that as the source for a new query, to total up these results

SELECT customer_ID, first_name, last_name, sum(hc_sales_total) as totalsales
FROM
(
    SELECT c.customer_ID, c.first_Name, c.last_name, 
    SUM(hc.cost) AS hc_sales_total
    FROM Customers c 
         INNER JOIN HairCuts hc
         ON c.customer_ID = hc.customer_ID
    GROUP BY c.customer_ID

    UNION ALL

    SELECT c.customer_ID, c.first_Name, c.last_name, 
    SUM(ps.cost) AS ps_sales_total
    FROM Customers c
         INNER JOIN ProductSales ps
         ON c.customer_ID = ps.customer_ID
    GROUP BY c.customer_ID
) sales
GROUP BY customer_ID, first_name, last_name

Of course, the inner grouping is superfluous, so

SELECT customer_ID, first_name, last_name, sum(cost) as totalsales
FROM
(
    SELECT c.customer_ID, c.first_Name, c.last_name, hc.cost
    FROM Customers c 
         INNER JOIN HairCuts hc
         ON c.customer_ID = hc.customer_ID

    UNION ALL

    SELECT c.customer_ID, c.first_Name, c.last_name, ps.cost
    FROM Customers c
         INNER JOIN ProductSales ps
         ON c.customer_ID = ps.customer_ID
) sales
GROUP BY customer_ID, first_name, last_name

Solution 2

If you want all three sums, you can do this using a union all/group by approach:

SELECT c.customer_ID, c.first_Name, c.last_Name,
       SUM(hp.hc_sales) as hp.hc_sales_total, SUM(hp.ps_sales) as hp.ps_sales_total,
       SUM(hp.hc_sales_total + hp.ps_sales_total) as Total
FROM ((SELECT hc.customer_ID, hc.cost AS hc_sales, 0 as ps_sales
       FROM HairCuts hc 
      ) UNION ALL
      (SELECT ps.customer_ID, 0, ps.cost AS ps_sales
       FROM ProductSales ps
      )
     ) hp JOIN
     Customers c
     on c.customer_ID = hp.customer_ID
GROUP BY c.customer_ID, c.first_Name, c.last_Name;

Note the ways that this query differs from yours:

1. The two join's have been factored out, so each subquery is working on only one table.
2. The join after the union all uses explicit join syntax, with the conditions going in the on clause.
3. There is only one aggregation.

Comments

[user3596312]

You sir, are a genius and I love you forever. Thank you. This works beautifully.

[Clockwork-Muse]

It's best to avoid the implicit-join syntax (comma-separated FROM clause) in favor of explicitly listing joins. Anyways, this will NOT get the correct answer, assuming more than one of either ProductSales or HairCuts.

[Clockwork-Muse]

Unless you escape object names, SQL (as a language) doesn't care about capitalization, so it doesn't matter (I'm assuming it's a typo from holding shift while getting the underscore). I'd avoid doing the subqueries as part of the SELECT, as this will (usually) run the statement for each row, and won't be as performant. Personally, I'd put the entire subquery in the COALESCE(...), as I feel it makes your intentions clearer (and what's going on more obvious).

[Clockwork-Muse]

Except this is going to give two rows per customerId, because you don't have an outer aggregate, but do have two rows from the UNION ALL.

[Gordon Linoff]

@Clockwork-Muse . . . There was another problem as well with the variable names. But the lack of outer group by -- I was thinking of two different ways to solve the problem, and ended up with an incorrect hybrid solution. It should work now.