Question about SUM and GROUP BY

[zxmgh]

Hi,

I have a generic question about SUM and GROUP BY:

Suppose I have a table called test1. Now I have to return the following information:

test1.col1, test1.col2, sum(test.col3) group by col2 only

I write the following query but it doesn't work and returns the "ORA-00979: not a GROUP BY expression" error, any idea how to make it work?

select test1.col1, test1.col2, sum(test.col3) from test1
where test1.col2 not null
group by test1.col2

Thanks

[gandolf989]

When you do a GROUP BY all of the columns bust either be a part of an aggregate function or a part of the GROUP BY clause.

Aggregate functions include, MIN(), MAX(), SUM(), AVG, STDDEV, etc.

[dbasan]

Hi,

I have a generic question about SUM and GROUP BY:

Suppose I have a table called test1. Now I have to return the following information:

test1.col1, test1.col2, sum(test.col3) group by col2 only

I write the following query but it doesn't work and returns the "ORA-00979: not a GROUP BY expression" error, any idea how to make it work?

select test1.col1, test1.col2, sum(test.col3) from test1
where test1.col2 not null
group by test1.col2

Thanks

execute this to avoid ora-00797:

select test1.col1, test1.col2, sum(test.col3) from test1
where test1.col2 not null
group by test1.col2,test1.col1;

[zxmgh]

dbasan,

Your code "group by test1.col2,test1.col1" is not what I want, I only want to group by test1.col2. Please clarify.

Thanks

[slimdave]

You'd better post some sample data and the result that you want

[dbasan]

dbasan,

Your code "group by test1.col2,test1.col1" is not what I want, I only want to group by test1.col2. Please clarify.

Thanks

the limitations of group by clause was clearly given by gandolf989.

as per the code you have given, i have already given you one solution, the next solution is:

select test1.col2, sum(test.col3) from test1
where test1.col2 not null
group by test1.col2;

Still it is not what you expect, then

I would suggest you better read the Oracle documents once again. Or

Go thru' this link http://www.dbasupport.com/forums/showthread.php?t=44794

[gandolf989]

You can also do this, but don't expect the results to be consistent.

SELECT test1.col1, FIRST(test1.col2) col2, SUM ( TEST.col3 )
  FROM test1
 WHERE test1.col2 IS NOT NULL
 GROUP BY test1.col2
 ORDER BY test1.col1;

[zxmgh]

Right now my query looks like this:

SELECT ch2.col1,
ch2.col2,
chq.col3_qty
FROM (SELECT ch1.col2, SUM(ch1.col3) AS col3_qty FROM test1 ch1
WHERE ch1.col2 IS NOT NULL
GROUP BY ch1.col2) chq
INNER JOIN test1 ch2 ON (chq.col2 = ch2.col2)

It did returns what I want, but is there a more efficient way to do this?

Thanks

[tamilselvan]

Right now my query looks like this:

SELECT ch2.col1,
ch2.col2,
chq.col3_qty
FROM (SELECT ch1.col2, SUM(ch1.col3) AS col3_qty FROM test1 ch1
WHERE ch1.col2 IS NOT NULL
GROUP BY ch1.col2) chq
INNER JOIN test1 ch2 ON (chq.col2 = ch2.col2)

It did returns what I want, but is there a more efficient way to do this?

Thanks

Today your query may return correct data b/c you have sample data.
One day your query will fail.
See below:

SQL> select * from state_city_sales ;

ST CITY                   SALE_AMT
-- -------------------- ----------
MA CONCORD                     100
NY CONCORD                     200
NY NEWYORK                     400
MA BOSTON                      300

SQL> get w1
  1  select b.state , b.city, a.tot_amt
  2  from  ( select city, sum(sale_amt) tot_amt
  3            from state_city_sales group by city  ) a
  4* inner join state_city_sales b on (a.city = b.city )
SQL> /

ST CITY                    TOT_AMT
-- -------------------- ----------
MA BOSTON                      300   
MA CONCORD                     300   ---> Wrong
NY CONCORD                     300   ----> Wrong
NY NEWYORK                     400

We must know the table definition, PK , FK etc and sample data before rewriting your SQL statement.

Tamil

[topgun]

try using this = hope it helps....

select a.state , a.city, sum(a.tot_amt)
from state_city_sales a
group by a.state , a.city

Today your query may return correct data b/c you have sample data.
One day your query will fail.
See below:

SQL> select * from state_city_sales ;

ST CITY                   SALE_AMT
-- -------------------- ----------
MA CONCORD                     100
NY CONCORD                     200
NY NEWYORK                     400
MA BOSTON                      300

SQL> get w1
  1  select b.state , b.city, a.tot_amt
  2  from  ( select city, sum(sale_amt) tot_amt
  3            from state_city_sales group by city  ) a
  4* inner join state_city_sales b on (a.city = b.city )
SQL> /

ST CITY                    TOT_AMT
-- -------------------- ----------
MA BOSTON                      300   
MA CONCORD                     300   ---> Wrong
NY CONCORD                     300   ----> Wrong
NY NEWYORK                     400

We must know the table definition, PK , FK etc and sample data before rewriting your SQL statement.

Tamil