CODEWITHSUNDEEP
mysqlgroup-byhaving
M. Reed
M. Reed

Reputation: 23

How to combine WHERE and HAVING in a MySQL query

How do I write a query for MySQL that would filter out where total pay is less than zero. I have tried different commands using 'HAVING' but I can't figure out the proper syntax.

This is my command:

SELECT tickets.idno, tickets.PAY, tickets.MATERIAL
FROM tickets
WHERE ( tickets.PAY <> 0  OR  tickets.MATERIAL <> 0 ) AND  tickets.DISTRICT = 'HO'  AND tickets.DATEPAID IS NULL  
ORDER BY tickets.NAME

with this result set:

HO0045  -140    0
HO2203  -45     0
HO2411  -5      0
HO2411  20      0
HO3448  -156    0
HO2519  2000    0
HO0075  -300    0
HO1669  -55     0
HO2666  -200    0
HO2666  -200    0
HO3447  -400    0
HO3447  400     0

This is result I get now, but it needs to eliminate those records where the total pay + total material for all rows for a given IDNO are less than zero. For example; all the rows for HO2411 and HO2519 should appear but not the rest.

Upvotes: 1

Views: 340

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562611

HAVING is meant to filter out whole groups, but to do that, you need to reduce the query with GROUP BY. But unfortunately you can't reverse the grouping operation after filtering groups.

So you have to do a subquery to filter the groups, then JOIN that result back to the table to find the rows with the corresponding idno:

SELECT t.idno, t.PAY, t.MATERIAL
FROM tickets AS t
INNER JOIN (
  SELECT idno, SUM(PAY) AS PAY, SUM(MATERIAL) AS MATERIAL
  FROM tickets
  GROUP BY idno
  HAVING PAY + MATERIAL < 0
) AS total USING (idno)
WHERE t.DISTRICT = 'HO' AND t.DATEPAID IS NULL  
ORDER BY t.NAME;

Upvotes: 1

Related Questions