如何“SUM”唯一列值并按日期过滤[重复]

Question

This question already has an answer here:

• how to calculate unique column values in mysql 2 answers

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 ?

</div>

Answer 1

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.