Geek Answers Handbook

SQL query to group by data, but with order by clause

I have a reservation table in which I have data

GUEST_NO    HOTEL_NO    DATE_FROM   DATE_TO     ROOM_NO
1           1           2015-05-07  2015-05-08  103
1           1           2015-05-11  2015-05-12  104
1           1           2015-05-14  2015-05-15  103
1           1           2015-05-17  2015-05-20  101
2           2           2015-05-01  2015-05-02  204
2           2           2015-05-04  2015-05-05  203
2           2           2015-05-17  2015-05-22  202

I want to get the result as. 1) It should show the output as Guest_no, Hotel_no, Room_no and a column counting as the number of repetitions of the previous three combinations of columns.

Therefore OutPut should

GUEST_NO    HOTEL_NO        ROOM_NO Count
1                1              103   2
1                1              104   1
1                1              101   1
2                2              204   1

etc .. But I want the result to be in an orderly way, for example: The output should be order by bk.date_to desc

My request looks like below, but I use it without working

select bk.guest_no, bk.hotel_no, bk.room_no,
       count(bk.guest_no+bk.hotel_no+bk.room_no) as noOfTimesRoomBooked
from booking bk 
group by bk.guest_no, bk.hotel_no, bk.room_no, bk.date_to
order by bk.date_to desc

Thus, with the addition of the order by the result, another is displayed, because when I added the order by date_ to the column, so I need to add this column, this is also a group sentence, which will ultimately be lower than shown below.

    GUEST_NO    HOTEL_NO        ROOM_NO Count
    1                1              103   1
    1                1              104   1
    1                1              103   1
    1                1              101   1
    2                2              204   1

, . , , date_to ,

+4
4

, guest_no, hotel_no room_no ( ) .

SELECT
    guest_no,
    hotel_no,
    room_no,
    COUNT(1) AS BookingCount
FROM
    booking
GROUP BY
    guest_no,
    hotel_no,
    room_no
ORDER BY
    MAX(date_to) DESC;
+6

, , ?

select
  guest_no,
  hotel_no,
  room_no,
  count(*) as Count
from
  booking
group by
  guest_no,
  hotel_no,
  room_no
order by
  min(date_to) desc

, , max() min(). SQL Fiddle: http://sqlfiddle.com/#!6/e684c/3

+2

.

select t.* from
(
select bk.guest_no, bk.hotel_no, bk.room_no, bk.date_to, 
count(*) as noOfTimesBooked from booking bk
group by bk.guest_no, bk.hotel_no, bk.room_no, bk.date_to
) t
order by t.date_to

date_to, . "group by", SQL Server "order by". , sub "order by" .

0
 SELECT * FROM 
(select bk.guest_no,bk.hotel_no,bk.room_no
,count(bk.guest_no+bk.hotel_no+bk.room_no) as noOfTimesRoomBooked,
(SELECT MAX(date_to) FROM booking CK
WHERE CK.guest_no=BK.guest_no AND bk.hotel_no=CK.bk.hotel_no
bk.room_no=CK.ROOM_NO ) AS DATEBOOK
from booking bk 
group by bk.guest_no,bk.hotel_no,bk.room_no,bk.date_to) A
ORDER BY DATEBOOK

0

More articles:


All Articles