Subquery and group by : Validation failed: mismatched input 'FROM' expecting <EOF>

Question

I want to calculate the cost by multiplying a price with the quantity of my equipment.

My data are like that :

idnt_eqpm	prx_tot_net	prix_pump_ht
10954847	10	8

And I want this data :

idnt_eqpm	cout_total	CA	idnt_eqpm_count
10954847	20	16	2

To do that someone told me that I have to use WITH statements and subquery.

Firstly I used a count for calculate the number of my equipment for each equipment.

After I calculate a cost by multiplying a price with my count field. I try this request :

`SELECT
     idnt_eqpm ,
     prx_tot_net ,
     prix_pump_ht ,
     cout_total ,
     CA ,
    COUNT( idnt_eqpm ) AS  idnt_eqpm_count 
FROM (
    SELECT prix_pump_ht * idnt_eqpm_count as cout_total,
    prx_tot_net * idnt_eqpm_count as CA,
    idnt_eqpm_count 
    FROM  cat_fa_ca_equipement_bte_test 
) AS T

GROUP BY  
     prx_tot_net ,
     prix_pump_ht ,
     cout_total ,
     CA`

But I have this error :

Validation failed: mismatched input 'FROM' expecting <EOF>

Someone can help me, I don't know how to use subquery with aggregations ?

Answer 1

I think a simple query using aggregate functions sum() and count() can do the trick :

select idnt_eqpm, sum(prx_tot_net)  as cout_total, 
                  sum(prix_pump_ht)  as CA,
                  count(idnt_eqpm) as idnt_eqpm_count
from cat_fa_ca_equipement_bte_test
group by idnt_eqpm