SUM with group by return error

Question

Having this two table

DEPARTMENT

//DEPARTMENT
D#          DNAME
-------------------
1         RESEARCH
2            IT
3          SCIENCE

PROJECT

//PROJECT
P#       D#        BUDGET
-------------------------
1001     1         22500
1002     1         22500
1003     3         50000

When i execute this command

SELECT d.D#,DNAME,sum(budget) as "TOTAL BUDGETS" 
    FROM DEPARTMENT d,PROJECT p 
WHERE d.D# = p.D#
GROUP BY d.D#;

I thought result will be

D#     DNAME     TOTAL BUDGETS
------------------------------
1     RESEARCH       45000
2        IT             0
3       SCIENCE      50000

but why i get this error

//Oracle: NOT A GROUP BY EXPRESSION ERROR

Farhad Jabiyev · Accepted Answer · 2014-04-27T09:24:43.273

2

All column names in SELECT list must appear in GROUP BY clause unless name is used only in an aggregate function.

Change query to:

SELECT d.D#,DNAME,sum(budget) as "TOTAL BUDGETS" 
    FROM DEPARTMENT d,PROJECT p 
WHERE d.D# = p.D#
GROUP BY d.D#, DNAME;

Update for the comment:

SELECT d.D#, d.DNAME, NVL(SUM(p.budget), 0) as "TOTAL BUDGETS" 
    FROM DEPARTMENT d LEFT OUTER JOIN PROJECT p ON d.D#=p.D#
GROUP BY d.D#, d.DNAME;

edited Apr 27 '14 at 09:24

answered Apr 27 '14 at 09:03

Farhad Jabiyev

26,014
8
72
98

okay. now fine. but if department dont have any budget should deisplay 0 . but why my query is just to display those have budget d? – user3553846 Apr 27 '14 at 09:07
@user3553846 I have updated the asnwer. Then you must use Left Outer Join. – Farhad Jabiyev Apr 27 '14 at 09:17
your result still didn't display any 0 if the department don't have the project – user3553846 Apr 27 '14 at 09:19
@user3553846: you'll need to replace `SUM(p.budget)` with `NVL(SUM(p.budget), 0)`. In Oracle, a `SUM` of no data is `NULL`, not zero. – Luke Woodward Apr 27 '14 at 09:20
@user3553846 I have updated the asnwer. You must add NVL to check if the value is null, then replace it with 0. – Farhad Jabiyev Apr 27 '14 at 09:21

score 0 · Answer 2 · answered Apr 27 '14 at 09:07

0

SELECT d.D#,max(DNAME),sum(budget) as "TOTAL BUDGETS" 
    FROM DEPARTMENT d,PROJECT p 
WHERE d.D# = p.D#
GROUP BY d.D#;

You should either include the field in GROUP BY section or use aggregate function (MAX or MIN is ok for the case)

answered Apr 27 '14 at 09:07

StanislavL

56,971
9
68
98

StarsSky · Answer 3 · 2014-04-27T09:23:02.910

You forget to declare the d.DNAME column in the GROUP BY statement:

SELECT d.D#,d.DNAME,sum(BUDGET) as "TOTAL BUDGETS" 
    FROM DEPARTMENT d,PROJECT p 
WHERE d.D# = p.D#
GROUP BY d.D#, d.DNAME;

SQLFiddle Example

If you want to display all departments, you need to use the LEFT JOIN

SELECT d.D#,d.DNAME,sum(BUDGET) as "TOTAL BUDGETS" 
    FROM DEPARTMENT d
LEFT JOIN PROJECT p
ON d.D# = p.D#
GROUP BY d.D#, d.DNAME;

SQLFiddle Example

To display 0 if no projects are present you need to use the CASE statement

SELECT d.D#,d.DNAME,
CASE WHEN sum(BUDGET) IS NULL
    THEN 0
    ELSE sum(BUDGET) END AS "TOTAL BUDGETS" 
FROM DEPARTMENT d
LEFT JOIN PROJECT p
ON d.D# = p.D#
GROUP BY d.D#, d.DNAME;

Output:

   D#   DNAME   TOTAL BUDGETS
    2   IT  0
    1   RESEARCH    45000
    3   SEARCH  50000

SQLFiddle Example

SUM with group by return error

3 Answers3