Question Locked

grouping in SQL

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Here is my error message:
(SQR 5528) ORACLE OCIStmtExecute error 979 in cursor 2:
ORA-00979: not a GROUP BY expression
SQL: SELECT DISTINCT wpymar1_pidm, spriden_last_name, spriden_first_name,
spriden_mi, spriden_id, (SELECT sum(cm.tbraccd_amount) FROM tbraccd cm,
tbbdetc WHERE tbbdetc_type_ind = 'C' AND cm.tbraccd_detail_code =
tbbdetc_detail_code AND t.tbraccd_pidm = cm.tbraccd_pidm GROUP BY
cm.tbraccd_pidm), (SELECT sum(pm.tbraccd_amount) FROM tbraccd pm,
tbbdetc WHERE tbbdetc_type_ind = 'P' AND pm.tbraccd_detail_code =
tbbdetc_detail_code AND t.tbraccd_pidm = pm.tbraccd_pidm GROUP BY
pm.tbraccd_pidm) FROM wpymar1 LEFT JOIN tbraccd t ON wpymar1_pidm =
t.tbraccd_pidm LEFT JOIN spriden ON t.tbraccd_pidm = spriden_pidm AND
spriden_change_ind is null WHERE spriden_last_name < 'Galzo' AND
spriden_last_name >= 'Gallaghe' GROUP BY wpymar1_pidm,
spriden_last_name, spriden_first_name, spriden_mi, spriden_id ORDER BY
spriden_last_name, spriden_first_name, spriden_mi, spriden_id

Error on line 136:
(SQR 3716) Error in SQL statement.
Just WHAT is not a GROUP BY expression?

3 replies

by marv732

Your inner queries both

select SUM(column_name)
GROUP BY column_name

If you want the result of each of these queries to be a single number then you need to remove the group by clause in both because there is nothing to GROUP BY since you are just creating a total of the column based on the where clause.
Try giving each of these queries an alias then add these aliases to the closing GROUP BY clause since there is no reference to either of these "columns" in the GOUP BY clause.

SELECT DISTINCT wpymar1_pidm, spriden_last_name, spriden_first_name,
spriden_mi, spriden_id,
(SELECT SUM (cm.tbraccd_amount)
FROM tbraccd cm, tbbdetc
WHERE tbbdetc_type_ind = 'C'
AND cm.tbraccd_detail_code = tbbdetc_detail_code
AND t.tbraccd_pidm = cm.tbraccd_pidm
GROUP BY cm.tbraccd_pidm) alias1,
(SELECT SUM (pm.tbraccd_amount)
FROM tbraccd pm, tbbdetc
WHERE tbbdetc_type_ind = 'P'
AND pm.tbraccd_detail_code = tbbdetc_detail_code
AND t.tbraccd_pidm = pm.tbraccd_pidm
GROUP BY pm.tbraccd_pidm) alias2
FROM wpymar1 LEFT JOIN tbraccd t ON wpymar1_pidm = t.tbraccd_pidm
LEFT JOIN spriden
ON t.tbraccd_pidm = spriden_pidm
AND spriden_change_ind IS NULL
WHERE spriden_last_name < 'Galzo'
AND spriden_last_name >= 'Gallaghe'
GROUP BY wpymar1_pidm,
spriden_last_name,
spriden_first_name,
spriden_mi,
spriden_id,
alias1,
alias2
ORDER BY spriden_last_name, spriden_first_name, spriden_mi, spriden_id

Greg

by john.a.wills

because the following works fine elsewhere in the same program:
select DISTINCT
high.tbraccd_pidm &pidm
(SELECT sum(c.tbraccd_amount) FROM tbraccd c, tbbdetc WHERE tbbdetc_type_ind = '
C' AND c.tbraccd_detail_code = tbbdetc_detail_code AND high.tbraccd_pidm = c.tbr
accd_pidm and c.tbraccd_term_code <= $term GROUP BY c.tbraccd_pidm)
(SELECT sum(p.tbraccd_amount) FROM tbraccd p, tbbdetc WHERE tbbdetc_type_ind = '
P' AND p.tbraccd_detail_code = tbbdetc_detail_code AND high.tbraccd_pidm = p.tbr
accd_pidm and p.tbraccd_term_code <= $term GROUP BY p.tbraccd_pidm)

What I have done which solves the problem is write:
sum((SELECT sum(cm.tbraccd_amount) FROM tbraccd cm, tbbdetc WHERE tbbdetc_type_i
nd = 'C' AND cm.tbraccd_detail_code = tbbdetc_detail_code AND wpymar1_pidm = cm.
tbraccd_pidm))
but, of course, that has no GROUP BY, so you may be right - except for the one which works, above.

This discussion is closed to new replies.