Bala Murugan Bala Murugan - 1 month ago 7
MySQL Question

How to "SUM" unique column Values And Filter By Date

INPUT

+------------+----------+-----------+-------+-------------+
| cardNo | userName | tablename | hours | date |
+------------+----------+-----------+-------+-------------+
| 1 | a | a | 12 | 12-06-2015 |
| 1 | a | a | 5 | 11-06-2015 |
| 2 | b | b | 3 | 15-06-2015 |
| 1 | a | a | 8 | 12-06-2015 |
| 2 | b | b | 3 | 21-06-2015 |
| 1 | a | a | 12 | 14-06-2015 |
| 2 | b | b | 10 | 8-06-2015 |
+------------+----------+-----------+-------+-------------+


cardNo is unique. I need to display all details and total hours for each card, like:

DESIRED OUTPUT

+--------+----------+-----------+-------------+
| cardNo | userName | tablename | totalhours |
+--------+----------+-----------+-------------+
| 1 | a | a | 37 |
| 2 | b | b | 16 |
+--------+----------+-----------+-------------+


MY QUERY

SELECT cardNo,sum(hours)
FROM yourtable
GROUP BY cardNo;


But i need totalHours >=20 are 'N'.

SELECT cardNo,sum(hours)
FROM yourtable where totalHours>=20
GROUP BY cardNo;


i cant get correct answer. if i specify totalHours>=1 it gives correct answer what can i do. and also i need php fetch code for that query..

Can anyone help ?

Answer

Use HAVING clause:

SELECT cardNo,sum(hours) as TotalHours
FROM yourtable
WHERE date  BETWEEN fromdate AND todate
GROUP BY cardNo
HAVING sum(hours) >=20

Using WHERE clause, you can get the total hours of each cardNo between those dates.