CODEWITHSUNDEEP
sqlpostgresqldbeaver
user5460621
user5460621

Reputation:

Create new table with number of incidents per month

Whats up mates , i have already started to learn SQL database thing and i am confused here . i have to create a table with number of incidents per month. I already know how to create table but the rest ?

SELECT
    EXTRACT(month FROM dateofcall) AS x,
    incidentnumber,
    dateofcall
FROM
    incidents
GROUP BY
    incidentnumber,
    x
ORDER BY
    x ASC;

But its not giving me the results of incidents number per month . =(

Upvotes: 2

Views: 662

Answers (2)

O. Jones
O. Jones

Reputation: 108746

It looks like you are grouping by too many items in your GROUP BY clause, and you are not COUNTing your incidents, just showing their details.

Try this:

SELECT EXTRACT(month FROM dateofcall) AS x,
       COUNT(*) AS incidents    
FROM
   incidents
GROUP BY
    EXTRACT(month FROM dateofcall)
ORDER BY
    EXTRACT(month FROM dateofcall)

Upvotes: 1

Dai
Dai

Reputation: 155390

SELECT
    EXTRACT(month FROM dateofcall) AS theMonth,
    COUNT(*) AS theNumberOfIncidents
FROM
    incidents
GROUP BY
    EXTRACT(month FROM dateofcall)
ORDER BY
    theMonth

Your original query wasn't counting anything. You were also grouping by incidentNumber which I assume is your primary-key, which is a nonsensical operation.

Due to a quirk in the SQL language you cannot use a column alias in GROUP BY statements, which is why you need to duplicate the EXTRACT(month FROM dateofcall) code.

Upvotes: 1

Related Questions