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.
| Author |
Topic |
|
Proofkyko
Starting Member
6 Posts |
 Posted - 2012-12-02 : 17:01:29
|
| Hello guys I am new in the forum and I registered because I need some help. My problem is this:I have a table with three columns, car plate, date and the cost of the trip. What I need to find is the car plate that gets a bigger profit in average. Each row of the table is a passenger so I suppose I need to sum the cost of each passenger and then divide by the number of them. The problem is I dont know how to do this for every car plate in order to find the biggest profit.. Any help? If you can help thank you |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-02 : 19:30:39
|
| Can you provide example and expected output? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-03 : 07:43:58
|
if table contain passenger info you can do something likeSELECT TOP 1 WITH TIES carplate,SUM(costoftrip)*1.0/Count(*) AS AvgCostFROM tableGROUP BY CarplateORDER BY AvgCost DESC You may add further filters using WHERE caluse if you need to consider average over timeperiod like year etc by using date field------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Proofkyko
Starting Member
6 Posts |
Posted - 2012-12-03 : 16:41:20
|
| @sodeep thank you but visakh16 just helped :) @visahk, awesome man, really grateful ! Do you think you can help me on this one: I need to find a passenger that participated in every trip from a certain place. My problem is this certain, once I do not know how to look for every possibility you understand? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-04 : 01:05:27
|
| ok...what are tables for trip,passenger etc? how is passenger related to trip? if you can provide the sample data from tables, i will provide you the solution.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Count and do the average