CODEWITHSUNDEEP
sqlsql-server-2008
PeteTheGreek
PeteTheGreek

Reputation: 729

Select from 2 tables and get count totals

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
  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

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

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

Results:
Holiday_id | holiday_name | total_balance | record_count
1 | Italy | 4250 | 2
2 | Russia | 6840 | 2
3 | Spain | 1500 | 1

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

UPDATE: I've update the sql command to reflect where I have got to, I now get an error: The multi-part identifier "h.holiday_id" could not be bound.

Upvotes: 0

Views: 92

Answers (3)

Michael
Michael

Reputation: 1028

I would make sure you're storing total_balance as money so you don't have to convert when displaying the data.

Even though you're using a left join, by checking that booking_status_id = '330' it will exclude all entries in Holiday_Ref without a corresponding Booking entry with a status of '330'. If that is desired behavior you might make that more explicit and use an inner join.

In your current query you have more columns selected than in your desired result set. This is what I might suggest:

select
    holiday_ref.holiday_id
   ,holiday_ref.holiday_name
   ,sum(booking.total_balance) as total_balance
   ,count(1) as record_count
from
    holiday_ref
inner join
    booking 
 on holiday_ref.holiday_id = booking.holiday_id
where
    booking.booking_status_id = '330'
group by
    holiday_ref.holiday_id
   ,holiday_ref.holiday_name

Upvotes: 1

Hardy
Hardy

Reputation: 1539

I don't really understand why need to join a table twice.

How about using GROUP BY , it will give the result as you expect for the SUM and COUNT.

Something like

SELECT
  h.holiday_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
WHERE
  b.booking_status_id = '330' AND h.holiday_id = b.holiday_id
GROUP BY h.holiday_id
ORDER BY h.holiday_id

Upvotes: 1

halfbit
halfbit

Reputation: 3964

SQL works with results: 

select * from HOLIDAY_REF

gives the table above

select * from BOOKING

gives the other table above

select * from HOLIDAY_REF a, BOOKING b  where a.holiday=b.holiday give a combined table

so - and thats prob your trick a 

select * from ( 
    select * from HOLIDAY_REF a, BOOKING b  where a.holiday=b.holiday gove a combined table
)

gives the same resultset, but ..

you can do something like 

 select *  from ( 
    select * from HOLIDAY_REF a, BOOKING b  where a.holiday=b.holiday gove a combined table
) where b.booking_status_id = '330' and a..... what ever

each select gives new small table to select from

the correct syntax depends on your database

Upvotes: 0

Related Questions