CODEWITHSUNDEEP
sql-servert-sql
Shipl0ad
Shipl0ad

Reputation: 11

Error in TSQL aggregate error

I want to count the number of etape an Employee work in a project

My table temps are:

Temps:

noProjet
noEtape
noEmploye
annee
mois
jrsHm

My query is

select 
    t.noEmploye, t.NoProjet, c.count
from 
    temps t
inner join 
    (select 
         noProjet, noEtape, count(*) as count
     from 
         temps
     group by 
         noProjet) c on t.noProjet = c.noProjet
order by 
    noEmploye

Error I get is

Msg 8120, Level 16, State 1, Line 1
Column 'temps.noEtape' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 0

Views: 51

Answers (3)

Steve Kass
Steve Kass

Reputation: 7184

Is this what you want?

select distinct
  noEmploye,
  noProjet,
  sum(noEtape) over (
    partition by noProjet
  ) as EtapesdeProjet
from Temps
order by noEmploye

Upvotes: 1

veljasije
veljasije

Reputation: 7102

Maybe you can do this by using next query:

select 
    t.noEmploye, 
    t.NoProjet, 
    c.count
from temps t
    inner join (select noProjet, noEtape, count(*) as count from temps group by noProjet, noEtape) as c 
        on t.noProjet = c.noProjet
order by noEmploye

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

The error is self explanatory. Remove noEtape column from subquery. If column is not in the group list you can not select it unless you apply some aggregations to that column:

select t.noEmploye, t.NoProjet, c.count
from temps t

inner join (select noProjet, count(*) as count
from temps

group by noProjet ) c on t.noProjet = c.noProjet
order by noEmploye

Or:

select t.noEmploye, t.NoProjet, c.count
from temps t

inner join (select noProjet, max(noEtape) as noEtape, count(*) as count
from temps

group by noProjet ) c on t.noProjet = c.noProjet
order by noEmploye

Upvotes: 1

Related Questions