MS SQL Query Help

navinbabu
navinbabu used Ask the Experts™
on
I have 3 tables

City ( CityId , Name )

Items( Itemsid , ItemsName , HotelId , CityId)

Hotels (HotelId ,HotelName)

Im trying to write a query to get Hotelname ,City Name,  Number of items in that hotel in that city in that database





select count(i.Itemid), h.HotelName, c.Name from Items i inner join Hotels h on i.HotelId = h.HotelId inner join City c on c.CityId = i.CityId

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
You need to a 'group by' clause for this to work :

select count(i.Itemid), h.HotelName, c.Name from Items i inner join Hotels h on i.HotelId = h.HotelId inner join City c on c.CityId = i.CityId
group by h.HotelName, c.Name

Author

Commented:
Can u please explain group by importance here

Author

Commented:
Error is

The text,ntext and image data type cannot be sorted expect when using IS null or like expression
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
When you use an aggregative function like count, max, etc... along with others columns, you need to tell the database how to group the rows before applying the aggregative function.

In this case the database will "create" a different group for each rows having the same hotelname and name, then apply count function on each of these groups.
Reza RadConsultant, Trainer

Commented:
group by is important because you must select each hotel in city , with number of items in the city
with group by hotelname you can select all hotels in each group and fetch number of them
Dale BurrellDirector

Commented:
If you don't group you get 1 row per item, but since you want to count items per hotel, city you need to group by hotel, city.

Group is basically saying I want 1 row for every distinct combination of the columns in the group by clause.

Of course if you need zero entries then you need a different solution because using group by only gives results where items exist.

Commented:
Could you provide column datatypes please ?
Dale BurrellDirector

Commented:
So where have you used text, ntext or image as the column datatype?
Reza RadConsultant, Trainer

Commented:
what is tables structure?

Author

Commented:
Im attaching the SQL
food-29jan.sql
Dale BurrellDirector

Commented:
Using text type for a name is not the best choice... this will potentially kill your performance... normally you can set the maximum size for such a field without any problems, but if you really wanted to allow for the possibility of a huge name then I would suggest varchar(max).

If you can re-type the column that will solve the problem.

Otherwise group by convert(varchar(max),Name) will solve the problem in this case.

Author

Commented:
Thanks guys ,

Changing the data type to varchar worked .

And Im completly lost here . I mean I was so confused on how this works .
Reza RadConsultant, Trainer

Commented:
change all name fields to varchar(max) instead of text
Dale BurrellDirector

Commented:
As another general comment, that collation is pretty much obsolete now, its a Windows specific collation from way back. Latin1_General_CI_AS is a more up to date collation to use.
Reza RadConsultant, Trainer

Commented:
group by will group the results by the field . so you grouped the resutls by hotel and city, so each hotel and city will have one row in result, and count(*) then will aggregate number of items for each row
Reza RadConsultant, Trainer

Commented:
does it make sense now?

Author

Commented:
So we are basically make a group of items with same hotel and city and counting the no of items in each group .

But ,

When the query start how does that go ..? I mean first and next .

Like I was blindly using the query and real wanted to know whats going on in the background
Consultant, Trainer
Commented:
first look at your query,
that query returned all rows , i mean all items per hotel per city and count will return 1 for each row

but in corrected query,
all rows will fetched at first
then result will grouped by city and hotel
so result will be like this:
hotel 1     city 1    item1
hotel 1     city 1    item 2
hotel 2     city  1   item3


group by hotel and city will result this:
hotel 1    city 1   2
hotel 2   city 1    1



Dale BurrellDirector

Commented:
It is a tricky concept to grasp - its based in maths set theory which is just a little bit complex. There are a few sites around which may help to clarify it though e.g. http://www.sql-tutorial.net/SQL-GROUP-BY.asp

Author

Commented:
Thanks a lot guys . And will writting the same in a Stored Procedure improve the perfomance ? I mean If i use the same for a autocomplete and use the same in the stored procedure will I get results faster ?
Dale BurrellDirector

Commented:
If you want to use that information in multiple places then you probably want a view rather than a stored procedure. Probably won't be any faster but makes things more manageable.

Author

Commented:
Hey dale_burrell ,

The link u gave me was very nice . Its pretty clear .

Thanks
Reza RadConsultant, Trainer

Commented:
you can set index on your joining fields for faster results

Author

Commented:
Sorry for this . I thought h.hotelName is the index .
Reza RadConsultant, Trainer

Commented:
joining fields are:
cityid
hotelID

Author

Commented:
Yea . We are joining thoose two to get Hotelname and City Name in the results right ? If Im correct and What do u mean by the indexing these two and how do that speed up things ?.

Dont mind. I was wanted to know these things but never got a chance
Dale BurrellDirector

Commented:
This is another complex area... you want to index on the columns that are being used to *find* the records. Its the finding that takes the time, and hence why you index a record so you can find it faster. Once its found you can extract all the information you like.

That said in your case I would add HotelName as an 'included column' in your index since that is the only other column you need in this case. Its not indexed, but the data is stored with the index meaning that it doesn't have to access the actual record at all. Very handy feature.
Dale BurrellDirector
Commented:
http://www.sqlteam.com/article/sql-server-indexes-the-basics

Also SQL Server comes with a Tuning Advisor, you just set up the query and then run the tuning advisor and it makes suggestions for good indexs. It works well for straight forward queries like yours.

Author

Commented:
Indexing is regular MS indexing stuff ? I mean in windows for example for a Drive . Will that store all the data like a structure or something

Author

Commented:
Thanks dale_burrell , reza_red

You guys clearly defined me the meaning of a expert.

Reza RadConsultant, Trainer

Commented:
Index is another area, I think you must close this question and ask another!
Reza RadConsultant, Trainer

Commented:
Glad to help,
Regards,

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial