CODEWITHSUNDEEP
mysqlsql
user2710234
user2710234

Reputation: 3225

SELECT count from table where datetime is today in another table

I have two tables named tickets and ticket_updates

in ticket_updates there is a datetime column, and tickets.ticketnumber is equal to ticket_updates.ticketnumber

There is multiple rows in ticket_updates per each row in tickets

I want to select the number of rows added in tickets based on the datetime column in ticket_updates

I have tried this query:

SELECT COUNT(*) as counter 
from ticket_updates 
where DATE(datetime) = '2014-11-03' 
group by ticketnumber 
limit 0,1

but it returns 3 which is wrong, as 8 rows have been added in the tickets table today

Upvotes: 0

Views: 5752

Answers (3)

SMA
SMA

Reputation: 37023

You need to join two tables like below:

SELECT COUNT(ticketnumber) AS counter
FROM tickets WHERE ticketnumber  in (
             SELECT distinct ticketnumber 
             FROM ticket_updates 
             WHERE DATE(datetime) = '2014-11-03')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Remove the group by if you want the total:

SELECT COUNT(*) as counter 
from ticket_updates 
where DATE(datetime) = '2014-11-03' ;

Use the group by if you want the number of updates per ticketnumber.

Use count(distinct ticketnumber) if you want the number of different ticket numbers changed today.

EDIT:

If you want today's date and you want to be able to take advantage of an index, then try:

where datetime >= date(now()) and datetime < date(now()) + interval 1 day

Upvotes: 1

Uriil
Uriil

Reputation: 12628

According to your comment, probably this is what you need:

SELECT count(*) FROM
 (SELECT ticketnumber
 FROM ticket_updates 
 GROUP BY ticketnumber
 HAVING DATE(min(datetime)) = '2014-11-03' ) as `t1`

Upvotes: 0

Related Questions