CODEWITHSUNDEEP
mysqlsqldatabase
rioualen
rioualen

Reputation: 968

SQL: how to combine 2 queries in one, using 2 different GROUP BY instructions

SQL beginner here. I would like to combine 2 different queries, I think I'm pretty close to the solution but I'm missing a piece.

I have a table that looks like this

v1  v2
A   D
A   E
A   D
B   E
B   E
B   D
C   E
C   E
C   E
C   D

I want to count the total number of occurrence of each combination, which I get like this:

SELECT v1,v2,count(*) AS 'count' FROM table GROUP BY v1,v2;

and results in:

v1  v2 count
A   D  2
A   E  1
B   E  2
B   D  1
C   E  3
C   D  1

I also want to count the total number of occurrence for each value of v1, which I get like this:

SELECT v1,count(*) AS 'count_2' FROM table GROUP BY v1;

and results in:

v1  count_2
A   3
B   3
C   4

Now I would like to combine both queries into one in order to get the following:

v1  v2 count count_2
A   D  2     3
A   E  1     3
B   E  2     3
B   D  1     3
C   E  3     4
C   D  1     4

I wrote this query but it's incomplete I believe:

SELECT v1,v2,count,sum(count) AS 'count_2' FROM (
    SELECT v1,v2,count(*) AS 'count' 
    FROM table GROUP BY v1,v2
) A
GROUP BY v1;

Basically I "lose" duplicates and end up with an incomplete table:

v1  v2 count count_2
A   D  2     3
B   D  1     3
C   E  3     4

What do I need to tweak here to get the desired output? Thank you for your help!

Upvotes: 0

Views: 55

Answers (3)

ysth
ysth

Reputation: 98508

This is easily done with sum as a window function (requires mysql 8.0+ or mariadb 10.2+):

select v1,v2,count(*) as count,sum(count(*)) over (partition by v1) as count_2
from `table`
group by v1,v2;

Upvotes: 1

webstudi0
webstudi0

Reputation: 73

Use INNER JOIN instead:

SELECT A.v1, A.v2, count(*), B.count_2
FROM new_table AS A
INNER JOIN  (
    SELECT v1, count(*) AS 'count_2' 
    FROM new_table GROUP BY v1
) AS B ON A.v1 = B.v1
GROUP BY A.v1, A.v2;

Upvotes: 1

Akina
Akina

Reputation: 42844

SELECT v1, t1.v2, t1.cnt1, t2.cnt2
FROM (SELECT v1, v2, COUNT(*) cnt1
      FROM test
      GROUP BY v1, v2) t1
JOIN (SELECT v1, COUNT(*) cnt2
      FROM test
      GROUP BY v1) t2 USING (v1)

fiddle

Upvotes: 1

Related Questions