GROUP BY day of Datetime SQL

Question

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 :)

It either has to be in a aggregate function e.g. `SUM / count / min / max` or in the `group by` — Matt, Mar 17 '16 at 05:14
I just wanna Group By the DATE of the LastUpdateTime. because I'm looking for total revenue everyday. — Anthony L., Mar 17 '16 at 05:15
How should I put it in the `group by` if I just wanna them to sum the revenue by days? — Anthony L., Mar 17 '16 at 05:16
It doesn't really make sense to include the `LastUpdateTime` in the `SELECT` list because multiple times will map to the same day. So I think you should remove it. — Tim Biegeleisen, Mar 17 '16 at 05:20
Please tag dbms product used. (You have some non-ANSI SQL constructions there...) — jarlh, Mar 17 '16 at 09:04

score 2 · Answer 1 · answered Mar 17 '16 at 05:19

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.

score 1 · Answer 2 · edited May 23 '17 at 12:31

1

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

edited May 23 '17 at 12:31

Community

1
1

answered Mar 17 '16 at 05:14

Moumit

8,314
9
55
59

Thanks, but both your solutions didn't work. Another error appears: `The conversion of a varchar data type to a datetime data type resulted in an out-of-range value` – Anthony L. Mar 17 '16 at 05:19
So it seems that `LastUpdateTime` column datatype is varchar.. right?? – Moumit Mar 17 '16 at 05:35

score 0 · Answer 3 · answered Mar 17 '16 at 05:35

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

GROUP BY day of Datetime SQL

3 Answers3