CODEWITHSUNDEEP
mysqlsqlgroup-by
Meera
Meera

Reputation: 149

grouping by non-database field

How can I group a query result by a field that is not saved in the database.

For example I want to group the result by duration which is came from subtraction of start time and end time.

here is how i find out the duration

date1= $row_TicketRS['CloseDate']; 
$date2 = $row_TicketRS['OpenDate'];

$diff = abs(strtotime($date2) - strtotime($date1)); 

$days = floor(($diff - $years * 365*60*60*24 - $months*30*60*60*24)/ (60*60*24));


if ( $days > 0)

{

$time1 = $row_TicketRS['OpenTime'];
$time2= $row_TicketRS['CloseTime'];

$t1=($time1); 
$t2=($time2);

$end=('14:30');
$start=('07:30');

$n = $end- $t1;

$n2 = $t2- $start;

$Hours2 = floor(($n+$n2)+(($days-1)*7));

echo   $Hours2.' Hours';

but know i do not know how to add it to the query

here is my query

$strQuery = "SELECT count(`ticket`.TicketID) as TotOutput, department.`DeptName` FROM `ticket`, `user`, department where ticket.OwnerID = user.EmpNo and  user.`DepartmentID` = department.`DepartmentID` and OpenDate between'".$DateFrom."' And '".$DateTo."'"

Upvotes: 0

Views: 114

Answers (3)

xkeshav
xkeshav

Reputation: 54052

dont put alias for hidden column , use directly

exmaple:

SELECT id, FLOOR(value/100)
  FROM tbl_name
  GROUP BY id, FLOOR(value/100);

Reference

MySQL permits expressions in GROUP BY clauses, so the alias is unnecessary:

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332641

It'd be better to have details, but a derived table/inline view would allow you to group by a computed value:

  SELECT x.duration, 
         COUNT(*)
    FROM (SELECT t.col, 
                 t.end_time - t.start_time AS duration
            FROM YOUR_TABLE t) x
GROUP BY x.duration

Upvotes: 2

mlemos
mlemos

Reputation: 1245

How about adding that computed value to the query with an alias like this:

SELECT some_fields, end - start AS duration FROM table ORDER BY duration

Upvotes: 0

Related Questions