MySQL Having Clause and Count()

Question

I've got the following table and I'm trying to retrieve every: name, descr, stock, address, postalcode & the city

where stock of item1 is > 10 AND stock of item2 is > 10 AND stock of item3 > 5

so I don't want the rows that only contain item1 and item2, like the ones with ID = 2

The rows of ID = 1 also won't fit because item1 its stock is < 10

Which means only rows of ID 3 and ID 4 match. But I don't want to see 6 rows, I just need two! containing the name, descr, stock, address, postalcode , city

| ID | NAME | DESCR | STOCK | ADDRESS | POSTALCODE | CITY |
|  1  |  foo  |  item1 |  5 |  addr1 |  po1 | city1 |
| 1  | foo  | item2 | 10 | addr1 | po1 | city1 |
| 1  | foo  | item3 | 5 | addr1 | po1 | city1 |
| 2  | bar  | item1 | 40 | addr2 | po1 | city1 |
| 2  | bar  | item2 | 30 | addr2 | po1 | city1 |
| 3  | smth  | item1 | 25 | addr3 | po3 | city1 |
| 3  | smth  | item2 | 20 | addr3 | po3 | city1 |
| 3  | smth  | item3 | 10 | addr3 | po3 | city1 |
| 4  | els  | item1 | 45 | addr4 | po4 | city1 |
| 4  | els  | item2 | 30 | addr4 | po4 | city1 |
| 4  | els  | item3 | 10 | addr4 | po4 | city1 |

I think I need something like:

SELECT name, descr, stock , address, postalcode, city
FROM table1
WHERE (descr like 'item1' AND stock >10) 
OR (descr like 'item2' AND stock >10) OR (descr like 'item3' AND stock >5)
GROUP BY name, descr, stock , address, postalcode, city
HAVING COUNT(distinct(somethingIdidNotFigureOutYet)) > 2

I tried to count the id, the name and even the description but it is not right. Could someone help me out with this one please. Thank you! (and sorry for the ugly table formatting)

What two rows do you want to see, when six rows match your conditions? — Gordon Linoff, Jan 11 '16 at 17:48

AdamMc331 · Accepted Answer · 2016-01-11T17:54:38.813

2

The way I usually approach problems like this is breaking them down into smaller groups, and then combining queries as necessary. Let's consider a few things:

Stock of item 1, item 2 must be greater than 10 and stock of item 3 must be greater than 5.

We can pull for rows that meet those requirements like this:

SELECT id
FROM myTable
GROUP BY id
HAVING 
  SUM(CASE WHEN descr = 'item1' THEN stock else 0 END) > 10 AND 
  SUM(CASE WHEN descr = 'item2' THEN stock else 0 END) > 10 AND 
  SUM(CASE WHEN descr = 'item3' THEN stock else 0 END) > 5;

This will return ids 3 and 4. Coincidentally, this will also work to meet the condition that we must have at least one item1, item2, and item3, since we used 'AND' in our having clause, which is what caused it to exclude id 2.

Here is an SQL Fiddle example.

edited Jan 11 '16 at 17:54

answered Jan 11 '16 at 17:48

AdamMc331

16,492
10
71
133

`DISTINCT` in your query is pretty useless – Pரதீப் Jan 11 '16 at 17:53
I suppose it is once I added the `GROUP BY`. – AdamMc331 Jan 11 '16 at 17:54
1

@McAdam331 Thank you very much! worked like a charm. Also I would like to everybody else for their effort! – b101 Jan 11 '16 at 18:58

score 1 · Answer 2 · answered Jan 11 '16 at 17:39

Given the WHERE filter in this case, you could restrict the resultset groups to those HAVING COUNT(DISTINCT descr) = 3 (or if descr is necessarily unique in each group, simply HAVING COUNT(*) = 3).

A more general solution might however be to explicitly verify that each criterion is satisfied by each group:

HAVING SUM(descr = 'item1' AND stock > 10)
   AND SUM(descr = 'item2' AND stock > 10)
   AND SUM(descr = 'item3' AND stock >  5)

This works because the boolean expressions within each SUM() evaluate to 1 if true and 0 if false; so taking the SUM() over each group results in 0 if false for all records or non-zero if true for any (and when used in the boolean context of the AND operations / HAVING clause, yields the desired result).

Whilst the resultset would be unaffected, I'd nevertheless retain the WHERE clause for performance reasons.

Pரதீப் · Answer 3 · 2016-01-11T17:48:37.493

You need to remove descr from select and group by and add it to Having clause to filter the groups

Try this.

SELECT ID,
       NAME, 
       stock, 
       address, 
       postalcode, 
       city 
FROM   table1 
WHERE  ( descr IN ( 'item1', 'item2' ) 
         AND stock > 10 ) 
        OR ( descr = 'item3' 
             AND stock > 5 ) 
GROUP  BY ID,
          NAME, 
          stock, 
          address, 
          postalcode, 
          city 
HAVING Count(DISTINCT descr) = 3

Note: Using LIKE operator without wildcard's doesn't make more sense so, I have changed it to = operator

score 1 · Answer 4 · answered Jan 11 '16 at 17:43

If you want the names and address information, then you can use a HAVING clause but you also have to fix the GROUP BY clause. Here is an example:

SELECT name, address, postalcode, city
FROM table1
GROUP BY name, address, postalcode, city
HAVING SUM(CASE WHEN descr like 'item1' THEN stock END) > 10 AND
       SUM(CASE WHEN descr like 'item2' THEN stock END) > 10 AND
       SUM(CASE WHEN descr like 'item3' THEN stock END) > 5;

Note: I have just moved all the conditions to the HAVING clause so the filtering logic is in one place. You can repeat it in the WHERE clause if you like.

Then, if you want the descriptions and stock amounts, one method is a list:

SELECT name, address, postalcode, city,
       GROUP_CONCAT(descr, ':', stock SEPARATOR '; ') as items
FROM table1
GROUP BY name, address, postalcode, city
HAVING SUM(CASE WHEN descr like 'item1' THEN stock END) > 10 AND
       SUM(CASE WHEN descr like 'item2' THEN stock END) > 10 AND
       SUM(CASE WHEN descr like 'item3' THEN stock END) > 5;

If you want the original rows, then I would suggest IN or EXISTS:

select t.*
from table1 t
where exists (select 1
              from table1 t1
              where t1.id = t.id and t1.descr = 'item1' and t1.stock > 10
             ) and
      exists (select 1
              from table1 t1
              where t1.id = t.id and t1.descr = 'item2' and t1.stock > 10
             ) and
      exists (select 1
              from table1 t1
              where t1.id = t.id and t1.descr = 'item3' and t1.stock > 5
             ) and
      t.descr in ('item1', 'item2', 'item3');

Note: this version assumes that items are not repeated for a given id. The first version doesn't make this assumption.

Is there a benefit to using `CASE WHEN descr LIKE 'item1'` instead of `WHEN descr = 'item1'` or was it just personal preference? — AdamMc331, Jan 11 '16 at 17:50
@McAdam331 . . . I almost never use the simple `case` statement because I've found too often that the logic needs to be modified to a searched case. It is simpler just to write the searched case version the first time. — Gordon Linoff, Jan 11 '16 at 17:53
That makes sense, so it's more 'scalable' in that regard, easier to change in the future? — AdamMc331, Jan 11 '16 at 17:55
@GordonLinoff thank you very much! I really appreciate it !! — b101, Jan 11 '16 at 18:59

MySQL Having Clause and Count()

4 Answers4