CODEWITHSUNDEEP
sqlsql-server
Mahajan344
Mahajan344

Reputation: 2550

SQL query to group by data but with order by clause

I have table booking 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

What I want is to get the result as. 1 ) It should show output as Guest_no, Hotel_no, Room_no, and column with count as number of time previous three column combination repeated.

So OutPut should like

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 result to in ordered way e.g.: The output should be order by bk.date_to desc

My query is as below its showing me count but if I use order by its not 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

So with adding order by result is showing different , because as I added order by date_to column so i have to add this column is group by clause too which will end up in different result as 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

Which is not the output I want. I want these four column but with order by desc of date_to column and count as no of repetition of first 3 columns

Upvotes: 4

Views: 95

Answers (4)

harsh pareek
harsh pareek

Reputation: 61

 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

IT MIGHT HELP YOU

Upvotes: 0

Hassan Salman
Hassan Salman

Reputation: 145

You could try this.

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

You will also have to select date_to and then group the result by it. If you use 'group by' clause, SQL Server doesn't allow you to use 'order by'. So you can make a sub query and use 'order by' in the outer query.

Upvotes: 0

James Z
James Z

Reputation: 12318

Maybe this is what you're looking for?

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

Or maybe max() instead of min(). SQL Fiddle: http://sqlfiddle.com/#!6/e684c/3

Upvotes: 2

Dylan Kerr
Dylan Kerr

Reputation: 76

I think a good way to do this would be grouping by guest_no, hotel_no and room_no, and sorting by the maximum (i.e. most recent) booking date in each group.

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;

Upvotes: 6

Related Questions