Reputation: 101
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
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
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
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