SQL: Count based on two conditions within one column

Question

I currently have a table which looks similar to this:

+----+----------+--+------------+
| id | entry_id |  |   value    |
+----+----------+--+------------+
|  1 |       20 |  | name       |
|  2 |       20 |  | email      |
|  3 |       20 |  | Menu 1     |
|  4 |       20 |  | 2020-04-23 |
|  5 |       21 |  | name       |
|  6 |       21 |  | email      |
|  7 |       21 |  | Menu 1     |
|  8 |       21 |  | 2020-04-23 |
+----+----------+--+------------+

I would now like to count the menus for a certain date which means that I would like to show "2" for menu 1 on 2020-04-23:

+-------+
| count |
+-------+
|     2 |
+-------+

My current SQL looks as follows:

SELECT COUNT(entry_id) FROM my_table WHERE value IN ("Menu 1", "2020-04-23") GROUP BY entry_id HAVING COUNT(*) = 2

However, this does not seem to work for me. Is there a way to count the entry once if two conditions are fulfilled?

Thanks for your help!

Most people here want sample table data and the expected result as formatted text, not as images. — jarlh
– jarlh, Commented Apr 23, 2020 at 15:52

Gordon Linoff · Accepted Answer · 2020-04-23 15:55:55Z

2

Hmmm . . . You have a very arcane data model. You have values, but no description of what they are. I would expect a "key" value somewhere.

You can get all the entries that match using:

select entry_id
from my_table
where value in ('2020-04-03', 'Menu 1')
group by entry_id
having count(*) = 2

And to get the count:

select count(*)
from (select entry_id
      from my_table
      where value in ('2020-04-03', 'Menu 1')
      group by entry_id
      having count(*) = 2
     ) e

answered Apr 23, 2020 at 15:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Benedikt W Over a year ago

there is actually a numeric value for each row, I just left it out. Thank you for your answer!

The Impaler · Accepted Answer · 2020-04-23 15:56:05Z

1

You can join the table with itself to find the rows you want.

For example:

select count(distinct id)
from my_table a
join my_table b on a.entry_id = b.entry_id
where b.value = '2020-04-23'
  and a.value = 'Menu 1'

answered Apr 23, 2020 at 15:56

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Collectives™ on Stack Overflow

SQL: Count based on two conditions within one column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related