Q
Problem solve Get help with specific problems with your technologies, process and projects.

Counting records using SQL Server 2000

I need to count rooms sold. The problem is that a room can be sold more than once for any given trip. How can I retrieve all records including one instance where the room has been sold multiple times on a given trip?

For example:

Inv. Trip Room
1 A50805 201
2 A50805 202
3 B60805 201
4 B60805 201

The query should return records 1, 2, 3.

Any help greatly appreciated!

It's a little know feature of the COUNT function that you can either count all of the values (the default behavior) or count only the distinct appearances of the values. You want to do the latter. For example:
   SELECT COUNT(DISTINCT room)
   FROM trip

Hope this helps…


Do you have comments on this Ask the Expert Q&A? Let us know.
This was last published in June 2005

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close