SQL : group by

how to display all column info when using group by ?

 

You could try using the MIN() function.

select bookid, count(bookid) as bookunit, sum(soldout) as soldout, min(bookname) as bookname, min(booksam) as booksam, ..... etc
from table1
group by bookid

As long as everything is the same for the same bookid then this should work.

 

Try following query..

SELECT BookName, BookSam, Local, PrintDate, BookId, COUNT(BookId) AS BookUnit, SUM(SoldOut) AS SoldOut FROM table1 GROUP BY BookId

 

The above won't work. As soon as you use "GROUP BY", everything in the select list has to be a column in the group by clause or an aggregation.

 

Second that. Everything that is not an aggregate calculation in the SELECT List must appear in the GROUP BY Clause also, otherwise the sql engine throws an error.

 

GROUP BY 'BookName' at the end of your query will do the same but order the book name and output like java, java, java, java, java, java, asp, asp

 

Hi Guys!

mastermunj 's SQL statement will work for mySQL but not in MSSQL/Oracle.

In mySQL it is not necessary to specify all the coulmns (which appears in select) in group by. But in Other databases you must ned to specify it.

for exaple:
-- //** This will work in mySQL but not in other databases **//--
SELECT COUNT(*), Field1,Field2 from TABLE GROUP BY Field1;

-- //** This will work for all databases **//--
SELECT COUNT(*), Field1,Field2 from TABLE GROUP BY Field1,Field2;

 

Yeah, I've read that MySQL is pretty flexible in what SQL it runs which can be a good thing or a bad thing (bad if you want to run the code in MSSQL or Orcale at a later date). ocm1 didn't say which SQL was being used so safer to use SQL that should work in all SQL engines.