MySQL:如何获得总和列的最大值?
所以我下面的查询..MySQL:如何获得总和列的最大值?
select rooms.id room_id, rooms.cnt, booked_dates.cnt, sum(booked_dates.cnt)
from rooms
LEFT JOIN booked_dates
on rooms.id = booked_dates.rid and
(booked_dates.start between '2013-05-06' and '2013-05-09')
where rooms.ht = 4 and rooms.id = 138
group by booked_dates.start
返回以下结果..
现在我需要将它们分组结果由room_id
并获得在该max
号sum(booked_dates.cnt)
列。因此,对于例如将是:
138 | 20 | 4 | 12
我将如何做到这一点?谢谢。
SELECT rooms.id room_id, rooms.cnt, booked_dates.cnt, SUM(booked_dates.cnt) AS booked_sum
FROM rooms
LEFT JOIN
booked_dates
ON rooms.id = booked_dates.rid
AND (booked_dates.start between '2013-05-06' and '2013-05-09')
WHERE rooms.ht = 4
AND rooms.id = 138
GROUP BY
booked_dates.start
ORDER BY
booked_sum DESC
LIMIT 1
公平足够没有想到订购它大声笑 – 2013-05-05 20:05:12
添加MAX()柱:
select
rooms.id room_id,
rooms.cnt,
booked_dates.cnt,
max(booked_dates.cnt) as max ,
sum(booked_dates.cnt) as sum
from rooms
LEFT JOIN booked_dates
on rooms.id = booked_dates.rid and
(booked_dates.start between '2013-05-06' and '2013-05-09')
where rooms.ht = 4 and rooms.id = 138
group by 1, 2, 3
你有你的组中的错误的条款 - 该查询修复了太(请注意,您可以参考列由他们位置,而不是他们的表达,按分组划分)。
不知道这将如何工作 – 2013-05-05 20:06:12
复制 - 通过它并尝试它,那么你会看到它是如何工作 – Bohemian 2013-05-05 20:32:36
我在列列表中看到一些非聚合字段......你确定这是你的意思吗?也许我只是不明白那些'cnt'列表示。 – fenway 2013-05-05 19:35:27