i would like to aggregate a table in sql-CodePudding

I would like to return the following

sales_person        year            count(sales_id)          avg(delivery_duration)
jim                 2019                 56                          5
jim                 2020                 34                          4
jim                 2021                 84                          3

how would i do this in sql

so far I have

select sales_person,
       extract(year from sales_date),
       count(sales_id),
       avg(delivery_duration)
from table

CodePudding user response：

I recommend to you to avoid using word table for table name. In my demo I used word test.
All you had to add in your try is a group by clause In the group by clause you HAVE to add all the columns you have selected and did not used in aggregate functions and you can
also add some extra columns from the table that are not in your select if you wish.
```
select sales_person,
    extract(year from sales_date),
    count(sales_id),
    avg(delivery_duration)
from test
group by sales_person
         , extract(year from sales_date)
```

DEMO

CodePudding user response：

Just as you posted it, just add group by for non-aggregated columns:

select sales_person,
       extract(year from sales_date),
       count(sales_id),
       avg(delivery_duration)
from table
group by sales_person,
         extract(year from sales_date)

CodePudding user response：

Your code is missing a GROUP BY clause:

select sales_person,
       extract(year from sales_date),
       count(sales_id),
       avg(delivery_duration)
from   table_name
GROUP BY
       sales_person,
       extract(year from sales_date)