Summarizing data in SQL 1

[realm174]

Hello everyone!

SQL isn't my cup of tea, I'm more of a Vb.NET guy. I can do simple INSERT or SELECT queries, but anything past that is beyond me. So I've come across an issue that I have no idea how to go about. I have data in a SQL Server (MS server 2008 if that matters), and each record looks like this (pulled from XML):

<FlightLog>
<Id>2249879</Id>
<Type>flight</Type>
<Time>2010-12-10 05:55:29.0</Time>
<Distance>133</Distance>
<Pilot>realm174</Pilot>
<Aircraft>C-FWHW</Aircraft>
<MakeModel>Lake Renegade</MakeModel>
<From>CZUM</From>
<To>CYYR</To>
<TotalEngineTime>2:05</TotalEngineTime>
<FlightTime>1:05</FlightTime>
<GroupName/>
<Income>1355.00</Income>
<PilotFee>0.00</PilotFee>
<CrewCost>0.00</CrewCost>
<BookingFee>0.00</BookingFee>
<Bonus>-69.09</Bonus>
<FuelCost>61.48</FuelCost>
<GCF>67.75</GCF>
<RentalPrice>174.00</RentalPrice>
<RentalType>tacho</RentalType>
<RentalUnits>111</RentalUnits>
<RentalCost>193.14</RentalCost>
</FlightLog>

What I need to do is summarize the information based on the <MakeModel> so that I can sort based on the best income per hour (FlightTime).

The net income for each record is calculated like this:

(<Income> + <Bonus>) - (<PiotFee> + <CrewCost> + <BookingFee> + <FuelCost> + <GCF> + <RentalCost>)

I guess what I'm trying to achieve is the equivalent of a MS Excel Pivot Table. I don't know ahead of time how many different <MakeModel> will be in the table, nor do I know the exact name for each.

Is this something that is achievable? Is this something that can be done in one query only?

Any help/pointers would be greatly appreciated, keeping in mind that I'm a SQL beginner.

thanks!


Cheers,

Realm174

 

[IanWaterman]

Assuming that your table is called FlightLog and the relevant fields are numbers and you are not going to query XML data.

Your SQL query to be run in SQL Server Management Studio would be something like

Select MakeModel,
sum((Income + Bonus) - (PiotFee + CrewCost + BookingFee + FuelCost + <CF + RentalCost)) as NetIncome
from Flight Log
Group By MakeModel

Ian

 

[realm174]

Thanks Ian, I'll give that a shot and modify to suit. I really appreciate the help!!

Cheers,

Realm174