SQL query of sums of joined table values

Question

Trying to get count of distinct rows in one query and in one row

table 1..

ID name 
1   a
2   b
3   c

Table 2

ID Parent status
1   1     0
2   1     1
3   1     0
4   1     0
5   1     2
6   2     0

desired result (count of distinct child elements)

ID   name 0a             1s               2s
1    a    3(count of 0s) 1 (counts of 1s) 2 (count of 2s)

Can we get this in one query..

What I have tried is result me values in 3 rows

Select t1.id, t1.name, count(status) from TABLE_1 t1 Left JOIN TABLE_2 t2
ON t1.id = t2.parent
group by status



ID   name status 
1    a    3
1    a    1
1    a    1
2    b    1

What do you use? MySql or SqlServer - the sql-dialects are differently implemented. It looks like you could use PIVOT on the joined tables: have a look here: https://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql — Patrick Artner, Nov 25 '17 at 18:46
Once you've figured out which RDBMS you're using, actually consider handling issues of data display in application code. — Strawberry, Nov 25 '17 at 18:47
I agree to that...I was thinking to confirm first...otherwise will handle them grouping within app — Luckyy, Nov 25 '17 at 18:52
BTW do u think m already getting max things I can have from mysql as per my question? — Luckyy, Nov 25 '17 at 18:53

score 1 · Answer 1 · answered Nov 25 '17 at 20:10

1

You can use conditional aggregation for this:

Select t1.id, t1.name, 
       coalesce(sum(status=0), 0) AS '0s', 
       coalesce(sum(status=1), 0) AS '1s',
       coalesce(sum(status=2), 0) AS '2s'
from TABLE_1 t1 
Left JOIN TABLE_2 t2 ON t1.id = t2.parent
group by t1.id, t1.name

Demo here

answered Nov 25 '17 at 20:10

Giorgos Betsos

71,379
9
63
98

A simpler and efficient approach. – Harshil Doshi Nov 25 '17 at 20:17

score 1 · Accepted Answer · answered Nov 26 '17 at 21:16

You can left join and use Count with a Case statement. For example:

SELECT T1.ID
      ,T1.name
      ,COUNT(CASE WHEN T2.status = 0 THEN T2.ID END) [0a]
      ,COUNT(CASE WHEN T2.status = 1 THEN T2.ID END) [1s]
      ,COUNT(CASE WHEN T2.status = 2 THEN T2.ID END) [2s]
  FROM TABLE_1 T1 LEFT JOIN TABLE_2 T2 ON T1.ID = T2.Parent
GROUP BY T1.ID, T1.name

Will produce output:

ID  name    0s  1s  2s
1   a       3   1   1
2   b       1   0   0
3   c       0   0   0

Here's the full code in sqlfiddle: http://sqlfiddle.com/#!6/ab92d/10/0

All three solutions worked, but this the query that took least time among all of them so marking it as answer. — Luckyy, Nov 27 '17 at 05:01

score 0 · Answer 3 · answered Nov 25 '17 at 18:53

0

Select t1.id, t1.name, 
       (select count(*) from t2 where t2.parent = t1.id and status=0) as 0s,
       (select count(*) from t2 where t2.parent = t1.id and status=1) as 1s,
       (select count(*) from t2 where t2.parent = t1.id and status=2) as 2s
       from t1 inner join t2
ON t1.id = t2.parent
group by t1.id;

answered Nov 25 '17 at 18:53

Harshil Doshi

3,497
3
14
37

I'll provide an alternative approach but it'll work if you don't have large dataset. – Harshil Doshi Nov 25 '17 at 18:55
I tested it, it works fine...just taking double time of a normal select query with just 10s records ..it might change with huge data. ...but thanks for ur direction – Luckyy Nov 25 '17 at 19:01

SQL query of sums of joined table values

3 Answers3