SQl : How to add new column by joining two same tables with aggregate values?

AKS 0
Viewed 26

The parent table has four variables ( ID, Type, Persons). Below output has been generated using distinct combination of 'ID' and 'Type' and aggregate as "counts' with no filters. I want to add another column next to it as 'missing_counts' based on filter, if 'Persons' value is Null in the same Table.

Id       Type            Counts
EUR      Accounts        3600
GBP      Accounts        3673
EUR     Science          1724
CNY     Physics         1608
GBP     Physics         4437
CNY     Chemistry       5070
EUR     Chemistry       1499
GBP     Chemistry       33752
EUR     Math            5155

The expected output

Id       Type            Counts  Missing_Counts
EUR      Accounts        3600     200
GBP      Accounts        3673
EUR     Science          1724      400
CNY     Physics         1608
GBP     Physics         4437
CNY     Chemistry       5070
EUR     Chemistry       1499      600
GBP     Chemistry       33752
EUR     Math            5155      800

My work so far:

SELECT distinct p.Id, p.Type, count (*) as Counts
  FROM Table1 as p
  group by p.Id, p.Type

  left join (SELECT distinct q.Id, q.Type, count (*) as missing_count
             FROM Table1 as q
             where Persons is Null
             group by q.Id, q.Type)
  ON p.Id = q.Id and p.Type = q.Type
mysql sql
1 Answers

you need to join subqueries

SELECT 
    p.Id, p.Type, p.counts, q.missing_count
FROM
    (SELECT DISTINCT
        p.Id, p.Type, COUNT(*) AS Counts
    FROM
        Table1 AS p
    GROUP BY p.Id , p.Type) p
        LEFT JOIN
    (SELECT DISTINCT
        q.Id, q.Type, COUNT(*) AS missing_count
    FROM
        Table1 AS q
    WHERE
        Persons IS NULL
    GROUP BY q.Id , q.Type) q ON p.Id = q.Id AND p.Type = q.Type
nbk0
Related