CODEWITHSUNDEEP
sqlcount
Lee Jun Wei
Lee Jun Wei

Reputation: 320

Count in SQL server

select
hotel.hotel_name 'Hotel Name',
Address 'Address',
Description 'Description',
Contact_No 'Contact Number'
from hotel, customer_profile, booking_details where
customer_profile.email = booking_details.email
and hotel.hotel_name = booking_details.hotel_name
and booking_details.email = '[email protected]'
order by 1

I can't add images /:

So for example currently my result is

+-----------------------+
| Name   | Address      |
+-----------------------+
| Hotel1 | Beach Road1  | 
| Hotel2 | Beach Road2  |
| Hotel2 | Beach Road2  |
+-----------------------+

I want it to remove the duplicates, and add a new column "Number of times" which indicate how many times it appeared.

I want it to be

+------------------------------------+
| Name   | Address     | No of Times |
+------------------------------------+
| Hotel1 | Beach Road1 | 1           |
| Hotel2 | Beach Road2 | 2           |
+------------------------------------+

Upvotes: 1

Views: 59

Answers (2)

Robert
Robert

Reputation: 25763

You should use for your examples group by clause a below

select
hotel.hotel_name "Hotel Name",
Address, count(1) "No of Times"
from hotel, customer_profile, booking_details where
customer_profile.email = booking_details.email
and hotel.hotel_name = booking_details.hotel_name
and booking_details.email = '[email protected]'
group by hotel.hotel_name, Address   
order by 1

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271031

First, learn proper join syntax, don't use single quote for column names, and use column aliases for all columns. Here is what I think your current query is doing.

select h.hotel_name as HotelName, h.Address. h.Description
       cp.Contact_No 
from booking_details bd join
     hotel h
     on h.hotel_name = bd.hotel_name join
     customer_profile cp
     on cp.email = bd.email
where bd.email = '[email protected]'
order by 1;

Next, to solve your question, just use group by. You can also eliminate the join to customer_profile, because I don't think that table gets used:

select h.hotel_name as HotelName, h.Address. count(*) as NumberOfTimes
from booking_details bd join
     hotel h
     on h.hotel_name = bd.hotel_name 
where bd.email = '[email protected]'
order by 1;

Upvotes: 1

Related Questions