group by

[hyousuf2]

i want to group the results of a querry according to a field called "Status",
i tried a few queries but the all dont work ! wat to do

[David.Poundall]

Try ...

SELECT * from YourTable GROUP BY Status

.

[David.Poundall]

Post any errors that you get should you get any.

[hyousuf2]

well i tried this one earlier but it does'nt work. heres wat it says
if i try like "select * from tableName groupby status" it says can't group on fields select with '*'
now if i try to give column names in place of '*' it says that the mentioned column names are not part of an aggregate function.

im using MS Access.( if it makes some difference )

[David.Poundall]

Hold on.

[David.Poundall]

Daft question it may seem - but is 'status' a column in the table that you are referencing. Reason I ask is that I have had this before with in a table join situation.

Also - are there any relationships to the table. I'll bet there are.

[David.Poundall]

Assuming that you have got relationships, and assuming theyare valid and the database is correctly normalised, you will have to add group by for ALL of the columns that you are returning in the recordset.

The first in th elist being the main grouping that you want.

here is what I had to do...

SELECT Prevac_Cycle.CycleRef,
	 Prevac_Cycle.TreatmentDate,
	 Prevac_Cycle.StartBSV,
	 Prevac_Cycle.FinBSV, 
	 Prevac_Cycle.FinBSV as UsedBSV,  
 	 Prevac_Cycle.OperatorEstCubed as Cubed,   	  
 	 Prevac_Cycle.OperatorEstCubed as RunCubed,   
 	 Prevac_Cycle.WorknetEstCubed as LperM3, 
	 CS_Previous.CycleID, 
       ProcessNotes.Bypass as Bypass,   
       ProcessNotes.EngNote1 as Engineering_Note1     
FROM (Prevac_Cycle INNER JOIN ProcessNotes ON Prevac_Cycle.CycleRef = ProcessNotes.CycleRef) INNER JOIN CS_Previous ON Prevac_Cycle.CycleRef = CS_Previous.CycleRef 
WHERE (Prevac_Cycle.TreatmentDate >= #startdate#)  
  AND (Prevac_Cycle.TreatmentDate <= #finishdate#)   
GROUP BY Prevac_Cycle.CycleRef, CS_Previous.CycleID, 
         Prevac_Cycle.TreatmentDate, Prevac_Cycle.StartBSV, Prevac_Cycle.FinBSV, Prevac_Cycle.OperatorEstCubed, 
         Prevac_Cycle.WorknetEstCubed,ProcessNotes.Bypass, ProcessNotes.EngNote1

Prevac_Cycle.CycleRef is what I really wanted to group by. But I needed to include the rest to get around the error message you came up against

[hyousuf2]

well first of all there are no relations to dat table, infact it doesnt have a primary key as well, n secondly if i try to group all the columns in the table it will return the complete table itself . ne other ideas ?

[szlamany]

When you use GROUP BY you must GROUP BY each and every field that is in the SELECT statement...

Or...

Put the field in the SELECT statement within an AGGREGATE function - such as SUM, MAX, MIN...

So you can say:

SELECT STATUS,SUM(1) FROM TABLEX GROUP BY STATUS

or SELECT STATUS,MAX(SOMEDATEFIELD) FROM TABLEX GROUP BY STATUS

[David.Poundall]

I think you need to use ORDER BY then. In the simple case you have it will have the same effect.

[hyousuf2]

hi
actually i found the problem myself. i needed to group by the columns on status n then sort the columns on status as well. ( took me 2 hours )

[David.Poundall]

Well done that man. It's a pain when your coding flow is halted by something that looks like it should be a no brainer though. We have all been there.

.