CODEWITHSUNDEEP
sqldatabasepostgresql
CodeYogi
CodeYogi

Reputation: 1422

SQL aggregate column issue

I need to find the top applicants from last week but I am getting errors in my SQL query.

  var queryString = "select id, created_at, user_id, count(id) as cnt from "+
      "applications where id in (select id from applications where "+
      "created_at > current_date - interval '1 week') group by user_id";

data.sql

insert into listings (id, created_at, created_by, name, description) values
    (1, '2015-01-15 11:00', 1, 'Join us conquering the world!', 'This is your best chance to be on the right side of the equation...')
;

insert into listings (id, created_at, created_by, name, description) values
    (1, '2015-01-15 11:00', 1, 'Join us conquering the world!', 'This is your best chance to be on the right side of the equation...')
;

insert into listings (id, created_at, created_by, name, description) values
    (2, '2017-01-29 11:00', 1, 'Join us conquering the world!', 'Holla ho')
;

insert into listings (id, created_at, created_by, name, description) values
    (3, '2017-01-15 11:00', 1, 'Join us conquering the world!', 'Hey ya')
;

insert into applications (created_at, user_id, listing_id, cover_letter) values
    ('2017-02-23 12:00', 2, 1, 'Hello, ...')
;

INSERT INTO APPLICATIONS (CREATED_AT, USER_ID, LISTING_ID, COVER_LETTER) VALUES
    ('2017-02-24 12:00', 2, 2, 'HELLO, ...')
;

INSERT INTO APPLICATIONS (CREATED_AT, USER_ID, LISTING_ID, COVER_LETTER) VALUES
    ('2017-02-22 12:00', 2, 2, 'HELLO, ...')
;

INSERT INTO APPLICATIONS (CREATED_AT, USER_ID, LISTING_ID, COVER_LETTER) VALUES
    ('2017-02-25 12:00', 3, 1, 'HELLO, ...')
;

And here is the error:

column "applications.id" must appear in the GROUP BY clause or be used in an aggregate function

What I am doing wrong?

So, basically I would like to see user id 2 with 3 applications and user id 3 with 1 application.

Upvotes: 0

Views: 109

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You might intend this query:

select user_id, count(id) as cnt
from applications
where id in (select id
             from applications 
             where created_at > current_date - interval '1 week'
            )
group by user_id;

Note the columns that are removed from the select.

If we assume that id is actually unique, you can do:

select user_id, count(id) as cnt
from applications
group by user_id
having max(created_at) > current_date - interval '1 week';

Upvotes: 1

Related Questions