select Multiple tables 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

General SQL Server Forums

New to SQL Server Programming

select Multiple tables count

Author

Topic

PeteLeHoq
Starting Member

37 Posts

Posted - 2013-10-05 : 19:57:37

I'm trying to get the number of records from one table where a column matches another column in a 2nd table. I then need the total values of another column that it has selected.

SELECT HOLIDAY_REF].holiday_id, COUNT([BOOKING].booking_status_id) AS record_count COUNT([BOOKING].total_value) AS total_value FROM [HOLIDAY_REF] LEFT OUTER JOIN [BOOKING] ON [HOLIDAY_REF].holiday_id = [BOOKING].booking_status_id WHERE [BOOKING].holiday_id=[HOLIDAY_REF].holiday_id && booking_status_id = '330'

Table 1 HOLIDAY_REF
holiday_id | holiday_name
1 | Italy
2 | Russia
3 | Spain

Table 2 BOOKING
holiday_id | booking_status_id | total_value
1 | 330 | 2500
3 | 330 | 1500
1 | 330 | 1750
2 | 330 | 1240
2 | 330 | 5600

Results would be:
Holiday_id | holiday_name | total_value | record_count
1 | Italy | 4250 | 2
2 | Russia | 6840 | 2
3 | Spain | 1500 | 1

Not sure I'm going about it the right way.

jethrow
Starting Member

37 Posts

Posted - 2013-10-06 : 00:12:16

[code]SELECT h.holiday_id,
h.holiday_name,
Sum(total_value) AS total_value,
Count(*) AS record_count
FROM HOLIDAY_REF AS h, BOOKING AS b
WHERE h.holiday_id = b.holiday_id
GROUP BY h.holiday_id, h.holiday_name;[/code]

Microsoft SQL Server Noobie

PeteLeHoq
Starting Member

37 Posts

Posted - 2013-10-06 : 10:01:06

Hmm, that didn't seem to do it. This is what I have got now, the total_value column is actually as varchar so I have to convert it to money. It's also called total_balance not total_value.

But this throws the error: The multi-part identifier "h.holiday_id" could not be bound.

SELECT h.holiday_id, h.holiday_name, CONVERT(Money,b.total_balance) AS total_balance, b.booking_status_id,
Sum(CONVERT(Money,b.total_balance)) AS total_balance,
Count(*) AS record_count

FROM [arend].[aren1002].[HOLIDAY_REF] AS h, [arend].[aren1002].[BOOKING] AS b

LEFT JOIN [arend].[aren1002].[booking]

ON h.holiday_id=booking.holiday_id

where b.booking_status_id = '330' AND h.holiday_id = b.holiday_id

ORDER BY h.holiday_id;

Any help much appreciated!
Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-06 : 10:46:19

If you do want to use a left join, change the code jethrow posted to this:

SELECT	h.holiday_id,
	h.holiday_name,
	Sum(CONVERT(money,total_balance)) AS total_balance,
	Count(*) AS record_count
FROM	HOLIDAY_REF AS h LEFT JOIN BOOKING AS b
	ON h.holiday_id = b.holiday_id
GROUP	BY h.holiday_id, h.holiday_name;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources