CODEWITHSUNDEEP
mysqlsql
user2029890
user2029890

Reputation: 2713

MySQL how to count the results of a count?

How do I construct a query in MySQL to count the results of a group? So,

Table A

Month    Item
Jan         1
Jan         3
Feb         2
Feb         2
Mar         3

Table B

Item   Color
1       red
1       blue
2       black
3       black
3       yellow

I want a query that will tell me how many items in Table A exist in Table B where there are at least two colors in each month. In other words, how many items have I sold in each month where there are more than 2 colors. So the result would be:

Month  Results
Jan      2
Feb      0
Mar      1

Thank you.

Upvotes: 1

Views: 233

Answers (3)

fthiella
fthiella

Reputation: 49049

This subquery returns all of the items that have at least two colors:

select Item
from TableB
group by Item
having count(distinct Color)>1

And you can join it with TableA, and the resulting query is this:

select
  A.Month,
  Count(B.Item)
from
  TableA A left join (
    select Item
    from TableB
    group by Item
    having count(distinct Color)>1) B
  on A.Item=B.Item
group by A.Month

Upvotes: 1

sgeddes
sgeddes

Reputation: 62841

Try something like this:

SELECT DISTINCT T.Month, COALESCE(T2.Cnt2,0) Cnt
FROM (
   SELECT Month
   FROM TableA
) T LEFT JOIN (
   SELECT A.Month, COUNT(DISTINCT B.Color) Cnt, COUNT(DISTINCT B.Item) Cnt2
   FROM TableA A
      INNER JOIN TableB B ON A.Item = B.Item
   GROUP BY A.Month
   HAVING  COUNT(DISTINCT B.Color) >= 2
) T2 ON T.Month = T2.Month

It Uses GROUP BY and HAVING to get the COUNT of DISTINCT Items in TableA and TableB. To return Feb 0, you need the LEFT JOIN.

Here is the SQL Fiddle.

Good luck.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

If I understand the question correctly, I think the following answers it:

select a.month,
       (case when count(distinct b.color) >= 2 then COUNT(distinct a.item) else 0 end)
from TableA a join
     TableB b
     on a.item = b.item
group by a.month

This counts the total number of colors in a month. If 2 or greater, then it has the number of items. Otherwise, it has 0.

Upvotes: 2

Related Questions