I'm having trouble with a MySQL query. If the label is "5" then I only want records where there is more than 10 of them for the current day, but any number of rows for other labels. What is the best way to accomplish this in the query?
SELECT id,name,label,date
FROM items
GROUP BY DATE('date'), label
HAVING COUNT(id) > 10 IF label = 5
I would do this in a single query:
SELECT id, name, label, date
FROM items
WHERE date >= date(now()) and date < date(now()) + interval 1 day
GROUP BY label
HAVING (label <> 5) or (COUNT(id) > 10);
This seems like the simplest method.
SELECT id,name,label,date
FROM items
WHERE label = 5 AND date = NOW()
GROUP BY label
HAVING COUNT(id) > 10
UNION ALL
SELECT id,name,label,date
FROM items
WHERE label != 5 AND date = NOW()
GROUP BY label
HAVING COUNT(id) > 0
This will return indeterminate values for columns not in the GROUP BY.
If I understand your question correctly, this query will give you what you need.
This query to work only if the definition of the "date" column has to be date type otherwise it will not work unless you cast the "date" column
SELECT label, COUNT(id) AS total
FROM items
WHERE label = 5 AND date = CURDATE()
GROUP BY label
HAVING COUNT(id) > 10
The question is confusing. based on your comment try this
SELECTs.total, i.* FROM
items AS i
LEFT JOIN (
SELECT label, COUNT(id) AS total
FROM items
WHERE label = 5 AND date = CURDATE()
GROUP BY label
HAVING COUNT(id) > 10
) AS s ON s.label = i.label
WHERE date = CURDATE()
