Required SQL Query

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?

Answer

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