blur with the joining tables - 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

blur with the joining tables

Author

Topic

see199
Starting Member

21 Posts

Posted - 2007-06-12 : 02:03:35

here's the question, i try to figure it out and test to run on the

m/s sql server. but i doesn't seems work.
car_type(carTypeID, carTypeName)
car(carID, carTypeID)
repair(repairID, carID, cost, date)

how do i find the total cost spent for repairing all cars, group by type, for this month?
how do i find the average cost spent for repairing each car, group by type, for this month?

this is my code, the wrong code.

select
c.carTypeID 'car type',
count(*) 'Total car',
select sum(cost) from repair,
sum(r.totalCost)/count(*)
from car C left join repair r on c.carID=r.carID
group by CT.carTypeID

let's say i got 20 nissan cars and the total repair is only 2 times, costing $100 each. so the average will be $200/20 which is $10.

can someone help me out? thx a lot :D

see199
Starting Member

21 Posts

Posted - 2007-06-12 : 02:24:40

the output suppose to be like this
(for the month of january)
car_type total cost average
nissan 20 200 10
honda 5 0 0
ford 7 140 20

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 02:31:38

[code]
select c.carTypeID, count(*), sum(r.cost), avg(r.cost)
from car c inner join repair r
on c.carid = r.carid
group by c.carTypeID
[/code]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-12 : 02:31:40

Something like this maybe:

Select 
 ct.CarTypeName as [Car Type],
 TotCars [Total],
 TotCost [Cost],
 CASE TotCards When 0 then TotCost Else TotCost/TotCars End as [Average]
From
(Select
 ct.CarTypeName,
 count(c.CarID) as TotCars,
 sum(r.cost) as TotCost
From car_type ct Join Car c on ct.carTypeID = c.carTypeID
Left Join repair r on c.carID = r.carID
group by ct.CarTypeName
) t

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-12 : 02:32:54

by 2 secs !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

see199
Starting Member

21 Posts

Posted - 2007-06-12 : 02:54:28

quote:

Originally posted by khtan


select c.carTypeID, count(*), sum(r.cost), avg(r.cost)
from   car c inner join repair r
       on c.carid = r.carid

thx. for the solution. maybe i din't make it clear

, what i mean is, if i have 20 nissan cars, but in tht specific months, only 2 car involve in repairing, and each repairing cost $100. which means tht the cost for the month is $200. The total nissan car is still 20. So the average repairing cost for each nissan car is $10.
sorry and thx again

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 03:08:23

have you tried harsh's solution ?

see199
Starting Member

21 Posts

Posted - 2007-06-12 : 03:36:19

quote:
Originally posted by khtan

have you tried harsh's solution ?

KH

i dun understand his

i'm so stupid

see199
Starting Member

21 Posts

Posted - 2007-06-12 : 03:43:50

after a few tries with ur guide, i came out with this:

select c.carTypeID,
count(*),
sum(r.cost),
sum(r.cost)/count(*), --the avg(r.cost) just divide the cost with 2, not the all 20 cars

from car c left join repair r
on c.carID = r.carID
-- THE DATE FILTER HERE --
--where r.date < '01/01/09'

group by c.carTypeID

so, it works. but there's another problem when i wanna add a date filter, which is in specific month. but this will cause the car group tht dun repair on tht month disapear.

see199
Starting Member

21 Posts

Posted - 2007-06-12 : 03:47:02

without the date filter
carType total cost average
nissan 20 200 10
honda 5 0 0
ford 1 100 100

after adding, it change the result
nissan 2 200 100
ford 1 100 100

suppose is the same if all the dates are b4 '01/01/09'
how to make it still the 1st result?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 04:17:04

maybe it will be easier if you can post some sample data from each of the tables and the required result

see199
Starting Member

21 Posts

Posted - 2007-06-12 : 04:39:55

hehe.. ok...

data:
car(carID, carTypeID)
SA1234, nissan;
SA1111, nissan;
SQ3232, nissan;
RS9888, nissan;
RQ4689, nissan;
BB1122, honda;
CC1212, ford;
DC3245, ford;

repair(repairID, carID, cost, date)
R1, SA1234, 150, 06/06/06;
R2, RQ4689, 100, 01/02/07;
R3, RS9888, 100, 03/02/07;
R4, DC3245, 50, 05/02/07

input:
enter date range: 01/01/07 - now

output:
cartype total cost average
nissan 5 200 40
honda 1 0 0
ford 2 50 25

hope this will make things easier :D

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-12 : 05:02:28

[code]
declare @car table(carID varchar(6), carTypeID varchar(10))
insert into @car
select 'SA1234', 'nissan' union all
select 'SA1111', 'nissan' union all
select 'SQ3232', 'nissan' union all
select 'RS9888', 'nissan' union all
select 'RQ4689', 'nissan' union all
select 'BB1122', 'honda' union all
select 'CC1212', 'ford' union all
select 'DC3245', 'ford'

declare @repair table(repairID varchar(2), carID varchar(6), cost decimal(10,2), [date] datetime)
insert into @repair
select 'R1', 'SA1234', 150, '06/06/06' union all
select 'R2', 'RQ4689', 100, '01/02/07' union all
select 'R3', 'RS9888', 100, '03/02/07' union all
select 'R4', 'DC3245', 50, '05/02/07'

select c.carTypeID,
total,
cost = isnull(cost, 0),
average = isnull(cost, 0) / total
from (
select c.carTypeID, total = count(*)
from @car c
group by c.carTypeID
) c
left join
(
select c.carTypeID,
cost = sum(cost)
from @car c inner join @repair r
on c.carID = r.carID
group by c.carTypeID
) r
on c.carTypeID = r.carTypeID
order by c.carTypeID
[/code]

see199
Starting Member

21 Posts

Posted - 2007-06-12 : 06:00:11

thank you so much :D finally i got it :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources