SQL COUNT ID by today, yesterday, this month, last month and this year-CodePudding

I have below table

What I need, is to count the ticket_id created today, yesterday, this month, last month and this year

So I'm using below

SELECT COUNT(`ticket_id`) AS `total_tickets_today`
FROM `tickets`
WHERE DATE(`ticket_created_at` = `ticket_created_at`) ;

But I get total of 0 (even though new tickets were added today)

data created

This is my php code

//Get Total tickets today
$sql_total_tickets_today = mysqli_query($mysqli,"SSELECT COUNT(`ticket_id`) AS    total_tickets_today FROM tickets WHERE DATE (ticket_created_at) = 'ticket_created_at' ;");
$row = mysqli_fetch_array($sql_total_tickets_today);
$total_tickets_today = $row['total_tickets_today'];

<p>OPEN Today:<strong> <?php echo number_format($total_tickets_today); ?></strong></p>
   <hr>
    <small>Yesterday: <?php echo number_format($total_tickets_yesterday); ?></small> | 
    <small>This Month: <?php echo number_format($total_tickets_month; ?></small> | 
    <small>Last month: <?php echo number_format($total_tickets_lastmonth); ?></small> | 
    <small>This year: <?php echo number_format($total_tickets_year); ?></small>

CodePudding user response：

SELECT COUNT(`ticket_id`) AS `total_tickets_today`
FROM `tickets`
WHERE DATEDIFF(day, DATE(`ticket_created_at`), DATE(NOW())) <= 1;

And then you change the parameter "day" in datediff and the value to which you compare the datediff to obtain the different values you are looking for.

CodePudding user response：

You can use current_date to compare any date with today's date:

SELECT COUNT(`ticket_id`) AS    total_tickets_today FROM tickets WHERE DATE (ticket_created_at) = current_date ;