Anthony L.
Anthony L.

Reputation: 101

GROUP BY day of Datetime SQL

I'm making a Sales report by PHP. The transaction date and time is on column LastUpdateTime with format: m-d-Y H:i:s. Now I wanna make report for 1 week, so I need the sum of TotalAmount of all transaction in 1 day, so I use group by DAY of LastUpdateTime. Here is my code but it doesn't work:

<?php
include 'report/go.php'; //DB connect
                                    $today =time();
                                    $from_time = date('d-m-Y', strtotime('last Monday'));
                                    $to_time = date('d-m-Y', strtotime('next Monday - 1 MIN'));
    $sql = "SELECT 
            SUM(TotalAmount) AS TotalPerDay , LastUpdateTime, DATEPART(dd,LastUpdateTime)
            FROM Tickets
            WHERE LastUpdateTime BETWEEN '$from_time' and '$to_time'
            GROUP BY DATEPART(dd,LastUpdateTime)
            ";
    $stmt = sqlsrv_query( $conn, $sql );
        if( $stmt === false) 
            {
                die( print_r( sqlsrv_errors(), true) );
            }
        while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) 
            {
                $money = $row['TotalPerDay'];
                $date = date_format($row['LastUpdateTime'], "d-m-Y");
                echo $money." - ".$date."<br>";
            }
?>

Error returned:

Column 'Tickets.LastUpdateTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Please help :) Thank you so much :)

Upvotes: 0

Views: 3943

Answers (3)

Anthony L.
Anthony L.

Reputation: 101

Thank you everyone, I figured out my solution

SELECT SUM(TotalAmount) AS TotalPerDay , CONVERT(VARCHAR(10),LastUpdateTime,103) AS TicketDateTime FROM Tickets WHERE LastUpdateTime BETWEEN '$from_time' and '$to_time' GROUP BY CONVERT(VARCHAR(10),LastUpdateTime,103)

I used CONVERT instead of DATEPART, then I still have the Sales date, and still able to GROUP BY the TotalAmount

Thanks everyone so much

Upvotes: 0

Utsav
Utsav

Reputation: 8093

So you are using lastUpdateTime and DATEPART(dd,LastUpdateTime) in select query, which are different. So for more than 1 lastUpdateTime, you can have same DATEPART(dd,LastUpdateTime). So if you group by DATEPART(dd,LastUpdateTime), then your Database would not know which lastUpdateTime should it return with it.

So either use max(lastUpdateTime) or any other aggregate function in select query.

SELECT 
        SUM(TotalAmount) AS TotalPerDay 
        ,max(LastUpdateTime)
        ,DATEPART(dd,LastUpdateTime)
        FROM Tickets
        WHERE LastUpdateTime BETWEEN '$from_time' and '$to_time'
        GROUP BY DATEPART(dd,LastUpdateTime),
        LastUpdateTime 

Or add lastUpdateTime to group by clause, but it would not give you expected result.

Upvotes: 3

Moumit
Moumit

Reputation: 9530

Either you use ..

SELECT      SUM(TotalAmount) AS TotalPerDay , DATEPART(dd,LastUpdateTime)
FROM        Tickets
WHERE       LastUpdateTime BETWEEN '$from_time' and '$to_time'
GROUP BY    DATEPART(dd,LastUpdateTime)

OR

SELECT      SUM(TotalAmount) AS TotalPerDay , DATEPART(dd,LastUpdateTime), LastUpdateTime
FROM        Tickets
WHERE       LastUpdateTime BETWEEN '$from_time' and '$to_time'
GROUP BY    LastUpdateTime

Because you can't select a column/computed result in group by select statement if it is not part of group by clause

If you want your query to execute then below link might help you

Efficiently Include Column not in Group By of SQL Query

Select a Column in SQL not in Group By

Upvotes: 1

Related Questions