Sql Exception ! Please Help ! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sql Exception ! Please Help !

Author

Topic

harry
Starting Member

3 Posts

Posted - 2004-07-26 : 06:59:53

Hi all,

What's wrong with this code ?
-------------------------------------------

SELECT LOTTERY,RATE,SUM(QNTY) AS QNTY,AMOUNT,ORG FROM LOTTERY,TRANS_SUM,ORG WHERE LOTTERY.LOTTID="+rs5.getString("LOTTID")+" AND LOTTERY.LOTTID=TRANS_SUM.LOTTID AND TRANS_SUM.STOCKISTID=3 AND TRANS_SUM.ORGID="+rs5.getString("ORGID")+" AND ORG.ORGID="+rs5.getString("ORGID")+" ORDER BY LOTTERY

It is throwing following exception...

java.sql.SQLException message given: General error: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause

-------------------------------------------

Please help. I am new to SQLServer/Sql query.

Thanks !

Harry
India

harry

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-26 : 07:02:59

To use aggregate functions (SUM,AVG etc) you need to use a GROUP BY clause in your SELECT, including all those columns that do not have an aggregate on them. So your code should look like...


SELECT LOTTERY,RATE,SUM(QNTY) AS QNTY,AMOUNT,ORG 
FROM LOTTERY,TRANS_SUM,ORG 
WHERE LOTTERY.LOTTID="+rs5.getString("LOTTID")+" AND LOTTERY.LOTTID=TRANS_SUM.LOTTID AND TRANS_SUM.STOCKISTID=3 AND TRANS_SUM.ORGID="+rs5.getString("ORGID")+" AND ORG.ORGID="+rs5.getString("ORGID")+" GROUP BY LOTTERY,RATE,AMOUNT,ORG ORDER BY LOTTERY"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources