Please help with sql query.

Question

People help ... There are two tables: Clients

---

idClient int login varchar

Messages

---

idMessage int dateWakeup datetime .... other fields

need for each client to count the number of entries in the table Messages in a given range of time. i tried something like this:

SELECT c.login, count(m.idMessage) FROM Clients c, Messages m where m.idClient=c.idClient and m.dateWakeup>'2010-09-01 00:00:01' and m.dateWakeup<'2010-10-01 00:00:01';

not working :((

Answer 1

try this

SELECT c.login, count(m.idMessage) FROM Clients as c
INNER JOIN Messages as m
ON m.idClient=c.idClient
WHERE m.dateWakeup BETWEEN '2010-09-01 00:00:01' and '2010-10-01 00:00:01'

Answer 2

Group by c.login

Answer 3

When using COUNT(...), you have to GROUP BY your results:

SELECT c.login, count(m.idMessage)
FROM Clients c, Messages m
where m.idClient=c.idClient
and m.dateWakeup>'2010-09-01 00:00:01'
and m.dateWakeup<'2010-10-01 00:00:01'
GROUP BY c.login

Answer 4

SELECT c.login, count(m.idMessage) FROM Clients c, Messages m  
where m.idClient=c.idClient and m.dateWakeup>'2010-09-01 00:00:01' and
m.dateWakeup<'2010-10-01 00:00:01' GROUP BY c.login;

Aggregate functions such as COUNT() requires a GROUP...BY