Required SQL Query

Question

I have been stuck on this for sometime now.

I have the following SQL Tables:

department table

Id  Name

 1  DeptA
 2  DeptB
 3  DeptC

users table

 Id   Name  Dept

 101  Alice 2
 102  Bob   3

alpha table

Id   Uid  Title

501  101  HELLO
502  102  HEY
503  101  SQL

beta table

Id   Uid  Title

601  101  HELLO1
602  101  HEY1
603  102  SQL1

Explanation:

There's basically a users table which has all the users.
Each user has a department (Dept field)
Each user has some records, linked to it with Uid, in alpha and beta tables.

The result I want:

DeptA DeptB DeptC

  0     4     2

I want the count of records in alpha and beta combined, grouped by Dept of the users whose records are there in these tables.

Can someone help me with the SQL query?

Does the result have to be pivoted into columns, or can you have a row for each department? — Barmar, Jul 27 '21 at 15:42
Use `UNION ALL` to combine `alpha` and `beta`. Join this with `department` and `users` table, and use `COUNT(*)` and `GROUP BY department.id` to get the total counts. — Barmar, Jul 27 '21 at 15:43
The result has to be in columns as the data in rows may be ambiguous. — Om Gupta, Jul 27 '21 at 15:44
See https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns for how to pivot from rows to columns. — Barmar, Jul 27 '21 at 15:44
How would it be embiguous if the result were a table with rows `DeptA 0`, `DeptB 4`, and `DeptC 2`? — Barmar, Jul 27 '21 at 15:45
[How to ask homework question](https://meta.stackoverflow.com/questions/334822/how-do-i-ask-and-answer-homework-questions) and [Open letter to students with homework problems](https://softwareengineering.meta.stackexchange.com/questions/6166/open-letter-to-students-with-homework-problems) — Barmar, Jul 27 '21 at 15:45
Hi @OmGupta Please check my below result. Hope it'll provide your desired result. — Rahul Biswas, Jul 27 '21 at 16:02

Rahul Biswas · Accepted Answer · 2021-07-27T16:26:34.613

As per your table structure I've used dept id for retrieving result otherwise I used dept name. You can also use COALESCE function if you get NULL

-- MySQL
SELECT SUM(CASE WHEN d.id = 1 THEN COALESCE(total, 0) END) dept_A
     , SUM(CASE WHEN d.id = 2 THEN COALESCE(total, 0) END) dept_B
     , SUM(CASE WHEN d.id = 3 THEN COALESCE(total, 0) END) dept_C
FROM department d
LEFT JOIN (SELECT u.dept
             , COUNT(1) total
        FROM users u
        INNER JOIN (SELECT uid
                    FROM alpha
                    UNION ALL
                    SELECT uid
                    FROM beta) t
                ON u.id = t.uid
        GROUP BY u.dept ) p
        ON d.id = p.dept;

Please check url http://sqlfiddle.com/#!9/020b2/1

Required SQL Query

1 Answers1