CODEWITHSUNDEEP
sqlpostgresqlsequelize.js
Muhammad Umer
Muhammad Umer

Reputation: 18097

Error: Id must appear in group by clause, Postgresql?

I've the following sql

SELECT *, min(okd.position) FROM

-- all deals
(SELECT * FROM "Deals" as d
WHERE (d.active = true) AND (d.latitude BETWEEN 40 AND 41) AND (d.longitude BETWEEN -75 AND -70)) AS okd

LEFT JOIN

-- used deals
(SELECT * FROM "Deals" as ad INNER JOIN
  (SELECT * FROM "DealsUsed" AS du WHERE du."CustomerId" = 1) AS rd
ON rd."DealId" = ad.id) as dd

--deals that were not used, grouped by user and sorted by position
ON dd."UserId" = okd."UserId" AND dd.position = okd.position
WHERE dd.id IS NULL
GROUP BY okd."UserId";

To me it looks like valid sql

Upvotes: 1

Views: 1185

Answers (1)

Littlefoot
Littlefoot

Reputation: 142743

SELECT * means "give me all columns" from table(s) involved in the FROM clause. I presume that it is not just UserId, but bunch of other columns as well. Which ones? Can't tell, as you SELECT * FROM "Deals". Consider avoiding SELECT * anywhere but for quick & dirty testing purposes.

Therefore, either enumerate all of them in the GROUP BY clause (which you probably don't want), or SELECT only UserId along with aggregated column; for example,

select okd."UserId", min(okd.position) 
FROM (your current FROM clause)
group by okd."UserId"

[EDIT, based on Oracle (as I have it), but applies to your database too]

Have a look at the following examples:

This works OK - I'm selecting department number and sum salaries of all employees who work in those departments:

SQL> select deptno, sum(sal)
  2  from emp
  3  group by deptno
  4  order by deptno;

    DEPTNO   SUM(SAL)
---------- ----------
        10       8750
        20       6775
        30       9400

I'd like to include job as well, i.e. sum salaries per department and job. If I include a new column into the SELECT but don't have it in GROUP BY, it'll fail:

SQL> select deptno, job, sum(sal)
  2  from emp
  3  group by deptno
  4  order by deptno;
select deptno, job, sum(sal)
               *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

Therefore, you have two options:

  • one is to revert back to the first query (i.e. remove JOB and have DEPTNO only), or
  • include additional column into the GROUP BY clause
SQL> select deptno, job, sum(sal)
  2  from emp
  3  group by deptno, job
  4  order by deptno, job;

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        20 ANALYST         3000
        20 CLERK            800
        20 MANAGER         2975
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600

[A LITTLE BIT MORE]

Yet another thing: there's a way to aggregate values without using the GROUP BY clause; in Oracle, that's what analytic functions do. I don't know whether there's something like that in the database system you use, but you might check it. Here's an example:

SQL> select deptno, ename, job, sum(sal) over (partition by deptno) sum_sal_dept
  2  from emp
  3  order by deptno, job;

    DEPTNO ENAME      JOB       SUM_SAL_DEPT
---------- ---------- --------- ------------
        10 MILLER     CLERK             8750
        10 CLARK      MANAGER           8750
        10 KING       PRESIDENT         8750
        20 FORD       ANALYST           6775
        20 SMITH      CLERK             6775
        20 JONES      MANAGER           6775
        30 JAMES      CLERK             9400
        30 BLAKE      MANAGER           9400
        30 TURNER     SALESMAN          9400
        30 WARD       SALESMAN          9400
        30 ALLEN      SALESMAN          9400
        30 MARTIN     SALESMAN          9400

See? Without the GROUP BY clause, I've calculated sum of salaries per departments.

Upvotes: 2

Related Questions