CODEWITHSUNDEEP
mysqldatabase
Guspan Tanadi
Guspan Tanadi

Reputation: 182

Select count dependent rows from multiple tables with union part 2

In part 2, i have two tables

first table from tb1, second from tb2.

+------+----------+------+
| kd   | skl      | name |
+------+----------+------+
| 001  | database | a    |
| 001  | web      | b    |
| 001  | web      | c    |
| 002  | app      | d    |
| 002  | web      | e    |
| 002  | app      | f    |
| 003  | json     | g    |
| 003  | -        | h    |
| 003  | -        | i    |
| 004  | ruby     | j    |
| 004  | database | k    |
| 004  | web      | l    |
| 005  | -        | m    |
| 005  | -        | n    |
| 005  | -        | o    |
+------+----------+------+

+------+----------+------+
| kd   | skl      | name |
+------+----------+------+
| 001  | database | p    |
| 001  | web      | q    |
| 001  | web      | r    |
| 002  | app      | s    |
| 002  | web      | t    |
| 002  | app      | u    |
| 003  | json     | v    |
| 003  | web      | w    |
| 003  | app      | x    |
| 004  | ruby     | y    |
| 004  | database | z    |
| 004  | web      | d    |
| 005  | -        | c    |
| 005  | web      | b    |
| 005  | app      | a    |
+------+----------+------+

it same look like my first question in Select count dependent rows from multiple tables with union

i take this query below.

    select kd,skl,name,sum(row) brs from
(select a.kd,skl,name,count(a.kd) row
from tb1 a where skl in('web','app')
group by a.kd,skl,name
union all
select b.kd,skl,name,count(b.kd)
from tb2 b where skl in('web','app')
group by b.kd,skl,name)t
group by kd,name,skl;

that select query give result

+------+-----+------+------+
| kd   | skl | name | brs  |
+------+-----+------+------+
| 001  | web | b    |    1 |
| 001  | web | c    |    1 |
| 001  | web | q    |    1 |
| 001  | web | r    |    1 |
| 002  | app | d    |    1 |
| 002  | web | e    |    1 |
| 002  | app | f    |    1 |
| 002  | app | s    |    1 |
| 002  | web | t    |    1 |
| 002  | app | u    |    1 |
| 003  | web | w    |    1 |
| 003  | app | x    |    1 |
| 004  | web | d    |    1 |
| 004  | web | l    |    1 |
| 005  | app | a    |    1 |
| 005  | web | b    |    1 |
+------+-----+------+------+

the problem is that brs column not count depends on kd column data.

i need the result below

+------+-----+------+------+
| kd   | skl | name | brs  |
+------+-----+------+------+
| 001  | web | b    |    4 |
| 001  | web | c    |    4 |
| 001  | web | q    |    4 |
| 001  | web | r    |    4 |
| 002  | app | d    |    6 |
| 002  | web | e    |    6 |
| 002  | app | f    |    6 |
| 002  | app | s    |    6 |
| 002  | web | t    |    6 |
| 002  | app | u    |    6 |
| 003  | web | w    |    2 |
| 003  | app | x    |    2 |
| 004  | web | l    |    2 |
| 004  | web | d    |    2 |
| 005  | web | b    |    2 |
| 005  | app | a    |    2 |
+------+-----+------+------+

please give select query example or a clue for this topic.

Many Thanks for all advices.

Upvotes: 0

Views: 81

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

This is not the most aesthetically pleasing query but it should work:

SELECT t1.kd, t1.sk1, t1.name, t2.brs
FROM
(
    SELECT a.kd, skl, name
    FROM tb1 a WHERE skl IN ('web','app')
    UNION ALL
    SELECT b.kd, skl, name,
    FROM tb2 b WHERE skl IN ('web','app')
) t1
INNER JOIN
(
    SELECT r.kd, SUM(r.row) brs
    FROM
    (
        SELECT a.kd, COUNT(a.kd) row
        FROM tb1 a WHERE skl IN ('web','app')
        GROUP BY a.kd
        UNION ALL
        SELECT b.kd, COUNT(b.kd)
        FROM tb2 b WHERE skl IN ('web','app')
        GROUP BY b.kd
    ) r
    GROUP BY r.kd
) t2
ON t1.kd = t2.kd

Upvotes: 1

Related Questions