Question 172048 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I print record count from 2 tables by grouping in SQL?

I need a sql query to print the count of records from different table for particular column.
I have tried the below but it is not working.

SELECT t1.names, 
  COUNT(t1.name) as Table1 count,
  COUNT(t2.FILENAME) as Table2 count
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.names = t2.names
GROUP BY t1.names

enter image description here

Tags:

2

Answers


  1. 0

    Use union all to merge both counts then pivot the result using the conditional aggregation :

    select name, MAX(case when tablecount = 'table1_count' then table_count else 0 end) as 'table1_count',
             MAX(case when tablecount = 'table2_count' then table_count else 0 end) as 'table2_count'
from (
  select 'table1_count' as tablecount, name, count(1) as table_count
  from table1
  group by name
  union all 
  select 'table2_count' as tablecount, name, count(1) as table_count
  from table2
  group by name
) as s
group by name

    Result :

    name    table1_count    table2_count
Naresh  2               3
serin   1               3
ravi    1               0

    Demo here

    Login or Signup to reply.
  2. 0

    You’re getting multiple counts because your tables have n-to-n relationship. Additionally, the inner join won’t grant you all the names, as not all the names can be found in both tables at the same time.

    One way to approach this problem is to:

    • make a union of all your records, and add a flag
    • apply a conditional aggregation inside a subquery
    SELECT names, 
       SUM(tab = 'tab1') AS tab1_cnt,
       SUM(tab = 'tab2') AS tab2_cnt
FROM (SELECT names, 'tab1' AS tab FROM Table1 
      UNION ALL
      SELECT names, 'tab2' AS tab FROM Table2) cte
GROUP BY names

    Output:

    names tab1_cnt tab2_cnt
    Naresh 2 3
    Srinivas 1 3
    Ravi 1 0

    Check the demo here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search