Using COUNT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using COUNT

Author

Topic

d473566
Starting Member

23 Posts

Posted - 2004-11-09 : 14:10:40

Hi

I have a table that looks like the following:

ID | BookingNum | inventAction |
---------------------------------
1 | 10 | booked
2 | 10 | booked
3 | 10 | booked
4 | 10 | unbooked
5 | 10 | unbooked
6 | 20 | booked
7 | 20 | booked
8 | 20 | unbooked
9 | 30 | booked
10 | 30 | unbooked

etc.

What I want is a recordset that will look like:

BookingNum | Booked | Unbooked
--------------------------------------------
10 | 3 | 2
20 | 2 | 1
30 | 1 | 1

I thought the following would do it, but it doesn't seem to work. (Instead of counting the number booked/unbooked for a particular booking number, it counts ALL the booked/unbooked in the table). It is close to what I am looking for, so I *think* I am close to getting it!

SELECT FlightAllocationHist.bookingNum
,booked = (SELECT COUNT(*)
FROM FlightAllocationHist
WHERE bookingNum = FlightAllocationHist.bookingNum
AND inventAction = 'booking')
,unbooked = (SELECT COUNT(*)
FROM FlightAllocationHist
WHERE bookingNum = FlightAllocationHist.bookingNum
AND inventAction = 'unbooking')
FROM FlightAllocationHist
GROUP BY FlightAllocationHist.bookingNum

Thanks in advance....

d473566
Starting Member

23 Posts

Posted - 2004-11-09 : 14:13:52

OK, solved it right after I posted. I had to alias the subqueries...

SELECT FlightAllocationHist.bookingNum
,booked = (SELECT COUNT(*)
FROM FlightAllocationHist b
WHERE b.bookingNum = FlightAllocationHist.bookingNum
AND inventAction = 'booking')
,unbooked = (SELECT COUNT(*)
FROM FlightAllocationHist c
WHERE c.bookingNum = FlightAllocationHist.bookingNum
AND inventAction = 'unbooking')
FROM FlightAllocationHist
GROUP BY FlightAllocationHist.bookingNum

amachanic
SQL Server MVP

169 Posts

Posted - 2004-11-09 : 14:30:26

This should do it more efficiently:

SELECT BookingNum,
SUM(CASE inventAction WHEN 'booked' THEN 1 ELSE 0 END) AS Booked,
SUM(CASE inventAction WHEN 'unbooked' THEN 1 ELSE 0 END) AS Unbooked
FROM FlightAllocationHist
GROUP BY BookingNum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources