Column 'Comptes.CO_NUMERO' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Question

I'm trying to do a group-by by "column 1" and "column 5" and Sum for columns 2,3,4

but it doesn't work . please help

this is my query

SELECT Post,Montant_Brut,Montant_AP,Net_1,Rubrique,OP_EXERCICE_COMPTA from(
select 
(case when left(C.CO_NUMERO,3) like '_9_' or left(C.CO_NUMERO,3) like '_8_' then (SUBSTRING(C.CO_NUMERO, 1, 1) + SUBSTRING(C.CO_NUMERO, 3, 1) + SUBSTRING(C.CO_NUMERO, 4, 1)) else left(C.CO_NUMERO,3) end ) as Post,
(case when left(C.CO_NUMERO,3) like '_9_' or left(C.CO_NUMERO,3) like '_8_' then sum (0) else (sum(C.CO_MONTANT)) end) as Montant_Brut,
(case when left(C.CO_NUMERO,3) like '_9_' or left(C.CO_NUMERO,3) like '_8_' then (sum(C.CO_MONTANT)) else sum(0) end) as  Montant_AP,
(case when O.OP_TYPE like 'Anouveaux' then (sum(C.CO_MONTANT)) else sum(0) end) Net_1,
(case when left(C.CO_NUMERO,2) like '_9' or left(C.CO_NUMERO,2) like '_8' then (SUBSTRING(C.CO_NUMERO, 1, 1) + SUBSTRING(C.CO_NUMERO, 3, 1)) when left(C.CO_NUMERO,2) like '25' then '24' else left (C.CO_NUMERO,2) end ) as Rubrique,
O.OP_EXERCICE_COMPTA 
from Operations O inner join Comptes  C on O.OP_ID = C.CO_OP_NUMERO WHERE ((C.CO_NUMERO between '21%' and '40%') or (C.CO_NUMERO  like '51%') ) and O.OP_EXERCICE_COMPTA = 11
) a group by Post, Rubrique

Answer 1

Let's try to help you.

First, you need to understand something with the group by clause. it's the group by that will make you able to count, or sum the results and still be able to keep a key.

So when you try to sums your c.co_montant, you loose the right to use c.co_numero like that.

Your query look almost good for me except that point.

Try out this version :

SELECT Post
,sum(Montant_Brut)
,sum(Montant_AP)
,sum(Net_1)
,Rubrique
,sum(OP_EXERCICE_COMPTA)
from(
    select 
    (case when left(C.CO_NUMERO,3) like '_9_' or left(C.CO_NUMERO,3) like '_8_' then (SUBSTRING(C.CO_NUMERO, 1, 1) + SUBSTRING(C.CO_NUMERO, 3, 1) + SUBSTRING(C.CO_NUMERO, 4, 1)) else left(C.CO_NUMERO,3) end ) as Post,
    (case when left(C.CO_NUMERO,3) like '_9_' or left(C.CO_NUMERO,3) like '_8_' then 0 else (C.CO_MONTANT) end) as Montant_Brut,
    (case when left(C.CO_NUMERO,3) like '_9_' or left(C.CO_NUMERO,3) like '_8_' then (C.CO_MONTANT) else 0 end) as  Montant_AP,
    (case when O.OP_TYPE like 'Anouveaux' then (C.CO_MONTANT) else 0 end) Net_1,
    (case when left(C.CO_NUMERO,2) like '_9' or left(C.CO_NUMERO,2) like '_8' then (SUBSTRING(C.CO_NUMERO, 1, 1) + SUBSTRING(C.CO_NUMERO, 3, 1)) when left(C.CO_NUMERO,2) like '25' then '24' else left (C.CO_NUMERO,2) end ) as Rubrique,
    O.OP_EXERCICE_COMPTA 
from Operations O inner join Comptes  C on O.OP_ID = C.CO_OP_NUMERO WHERE ((C.CO_NUMERO between '21%' and '40%') or (C.CO_NUMERO  like '51%') ) and O.OP_EXERCICE_COMPTA = 11
) a group by Post, Rubrique

I took your sum action and putted then in the result of the group by clause.

As long as I can't test that code, I would like you to tell me if it's working.