CODEWITHSUNDEEP
phpmysqlarrays
Mark Henry
Mark Henry

Reputation: 2699

mysql group results to single table row

I have this query which returns for a resort (res_id) the maximum temperature for the next three days (temphi at t0, t1, t2). I would like to have the forecast in one row per resort (for multiple resorts). How can I do that?

Query result

res_id  lud             temphi
1       2017-02-05      -5
1       2017-02-06      -2   
1       2017-02-07      -8
4       2017-02-05      2
4       2017-02-06      1
4       2017-02-07      -1

desired result

res_id      t0      t1      t2
1           -5      -2      -8
4           2       1       -1




$rQuery1 = "SELECT a.lud, a.temphi,a.res_id
FROM sv_snowalert a
LEFT JOIN sv_orte b ON a.res_id = b.res_id AND b.ski_id>0
INNER JOIN sv_canton c ON b.can_id = c.can_id
INNER JOIN sv_country d ON c.cou_id = d.cou_id
WHERE a.lud>='2017-02-05' GROUP BY a.res_id, a.lud ORDER BY a.res_id, a.lud ASC";
$rResult1 = mysql_query($rQuery1);

$seq=0;
$num_rows = mysql_num_rows($rResult1)
while ($rows1 = mysql_fetch_array($rResult1))
{
$seq++;
 // output

}

Upvotes: 0

Views: 36

Answers (1)

Blank
Blank

Reputation: 12378

Here just a mysql hint:

select
    a.res_id,
    max(case when date(a.lud) = date_add(curdate(), interval 0 day) then temphi else null end) as t0,
    max(case when date(a.lud) = date_add(curdate(), interval 1 day) then temphi else null end) as t1,
    max(case when date(a.lud) = date_add(curdate(), interval 2 day) then temphi else null end) as t2
from sv_snowalert a
left join sv_orte b on a.res_id = b.res_id and b.ski_id > 0
inner join sv_canton c on b.can_id = c.can_id
inner join sv_country d on c.cou_id = d.cou_id
where a.lud between curdate() and date_add(curdate(), interval 2 day)
group by a.res_id
order by a.res_id asc

and a demo here.

Upvotes: 1

Related Questions