Sum the product of two fields

  • Thread starter Thread starter Enrique Rojas
  • Start date Start date
E

Enrique Rojas

I have an interesting SQL to write and not sure how to go about it.

I need to sum the product of a two fields.

Table Pricing

Provider driver_id conversion accomp_id year_1_accom
E&A 5 1 1 2.2
E&A 5 1 2 1.1
E&A 5 0.5 3 2.2
PM 5 1 4 2.0
E&A 6 2 1 0.2
Telcom 6 1 2 2.0
Telcom 6 1 3 2.0
Telcom 6 0.5 4 2.0
Intn 6 1 5 3.0
Intn 6 2 6 0.5

conversion * year_1_accom = dollars
i want to add the sum of this dollars per provider

Provider driver_id year_1_total
E&A 5 3.3
PM 5 2.0
E&A 6 0.4
Telcom 6 5.0
Intn 6 1.0

I have tried
select provider, driver_id, sum (conversion * year_1_accom) as
year_1_total from Pricing group by Provider, driver_id


But I get an aggregate error in access
Thanks in advance.
 
SELECT provider,
driver_id,
Sum(conversion*year_1_accom) AS year_1_total
FROM pricing
GROUP BY provider, driver_id;

This worked fine for me.

If it still doesn't work then post the SQL of your Query and the exact
error message you are receiving.

Cheers,
Jason Lepack
 
SELECT provider,
driver_id,
Sum(conversion*year_1_accom) AS year_1_total
FROM pricing
GROUP BY provider, driver_id;

This worked fine for me.

If it still doesn't work then post the SQL of your Query and the exact
error message you are receiving.

Cheers,
Jason Lepack
Click to expand...

Yes it works. I have this query attached to a cascade of inner joins
that messed it up. Thanks for your input it got me thinking.

Enrique Rojas
 
Back
Top