SQL: insert total count row after joining table-CodePudding

The first step is to join staff and customer together. The second step is to count the distinct product_id. My target is to add the total(sum) field under the result table. Thanks.

staff

staff_ID     Name     cust_id          
1            Tom       101   
1            Tom       101
1            Tom       105
2            Peter     102
2            Peter     104
3            Billy     103

customer

cust_id    product_id        
101             A1
102             A2
103             A3
104             A4
105             A5

My work:

SELECT a.staff_name,COUNT(DISTINCT a.product_id)
FROM (SELECT distinct a.staff_id, a.staff_name, a.cust_id
     FROM staff)a
LEFT JOIN customer b ON a.cust_id=b.cust_id
GROUPBY a.staff_name

What I want is to add the total column below the count.

Name     count         
Tom       2   
Peter     2
Billy     1
Total     5

CodePudding user response：

Update:

Regarding the "Total", as @MatBailie correctly pointed out in the comments:

The aggregate of multiple COUNT(DISTINCT) rows CAN NOT be guaranteed to be summable. If two staff members share the same product_id the summary value will be LESS THAN the sum of its members.

So for this sample data set:

db<>fiddle here

| cust_id | product_id | |-------- | ----------------------| | 101 | A1 | | 102 | A2 | | 103 | A3 | | 104 | A4 <== Same product | | 105 | A5 | | 105 | A4 <== Same product |

Using GROUP BY ROLLUP yields a "Total" value of 5:

SELECT  COALESCE(a.staff_name, 'Total') AS Staff_Name
      , COUNT(DISTINCT b.product_id) AS [Count]
FROM   staff a LEFT JOIN customer b ON a.cust_id=b.cust_id
GROUP BY ROLLUP (a.staff_name);

Results:

 Staff_Name | Count
 :--------- | ----:
 Billy      |     1
 Peter      |     2
 Tom        |     3
 Total      |     5 **

Whereas calculating a simple sum of the totals, yields a "Total" value of 6. So just be aware of the difference.

 Staff_Name | Count
 :--------- | ----:
 Billy      |     1
 Peter      |     2
 Tom        |     3
 Total      |     6 **

Original (Wrong Answer):

Can't remember where I saw this answer, but ... assuming Staff_Name is never null, you could use GROUP BY ROLLUP to obtain the total. ~~That said calculating grand totals is usually more of a front-end job.~~

SELECT  COALESCE(a.staff_name, 'Total') AS Staff_Name
      , COUNT(DISTINCT b.product_id) AS [Count]
FROM   staff a LEFT JOIN customer b ON a.cust_id=b.cust_id
GROUP BY ROLLUP (a.staff_name);

CodePudding user response：

Try this one:

SELECT s.staff_name, COUNT(DISTINCT b.product_id), SUM(count) Total
FROM staff s
INNER JOIN customer b ON b.cust_id = s.cust_id
GROUP BY s.staff_name