Sum the product of two fields

[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.

 

[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

 

[Enrique Rojas]

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

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