SQL Count

[tdavis1]

I am knew to SQL and some help.
I have written a query

select CustomerNBR, Room, CheckinDt
from table a, table b
where checkindt between '07/01/2010' and '07/31/2010'
group by CustomerNBR, Room, CheckinDt

The reason I grouping by all three is a person might have multiple checkins on to the same room or different room on different dates and times. See below. What I need is a count by room number. (i.e B03 1, B04 4, B05 1) but I keep geeting B03 1
B04 4
B04 4
B04 4
B04 4
B05 1
I know why b/c of my grouping on the customer, but I need it in the grouping. Please help!!!!!
Cst Room Date
A B03 7/1/2010
B B04 7/2/2010
B B04 7/3/2010
C B04 7/4/2010
D B04 7/5/2010
E B05 7/6/2010
F C02 7/7/2010
G C03 7/8/2010
H C03 7/9/2010
H C03 7/10/2010
H C03 7/11/2010
I C05 7/12/2010
J CV1 7/13/2010
K CV1 7/14/2010
L CV2 7/15/2010
M CV3 7/16/2010
N CV4 7/17/2010
O CV4 7/18/2010
O CV4 7/19/2010
P CV4 7/20/2010

 

[markros]

Do you have two tables or one?

In your query, try to change

select CustomerNBR, Room, CheckinDt
from table a, table b
where checkindt between '07/01/2010' and '07/31/2010'
group by CustomerNBR, Room, CheckinDt

To

select CustomerNBR, Room, CheckinDt, count(*) as TotalRoomChecks, count(distinct(CustomerNBR)) as TotalCustomers
from table a
where  checkindt >= '20100701'  and CheckInDt <20100801'
group by CustomerNBR, Room, CheckinDt

PluralSight Learning Library

 

[tdavis1]

It's coming from two different tables. I only need a count of of the rooms by the customer.

 

[markros]

Your current query is using a CROSS JOIN between these two tables. You may need to specify the real JOIN condition to join the tables correctly.

PluralSight Learning Library