SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Count and do the average		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Proofkyko
Starting Member

6 Posts
Posted - 12/02/2012 :  17:01:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 12/02/2012 :  19:30:39  Show Profile  Reply with Quote
Can you provide example and expected output?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 12/03/2012 :  07:43:58  Show Profile  Reply with Quote
if table contain passenger info you can do something like

SELECT TOP 1 WITH TIES carplate,SUM(costoftrip)*1.0/Count(*) AS AvgCost
FROM table
GROUP BY Carplate
ORDER 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 MVP
http://visakhm.blogspot.com/
Go to Top of Page

Proofkyko
Starting Member

6 Posts
Posted - 12/03/2012 :  16:41:20  Show Profile  Reply with Quote
@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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 12/04/2012 :  01:05:27  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 12/04/2012 :  06:04:52  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Follow at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=181149

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000