GROUP BY makes my query fail

[fife]

I have the query below which works great.  Alls I want to do is all the projects from the query grouped by the description.

 

 
 $query_rs_installs = "SELECT    Calls.Call_Ref, Calls.Link_to_Contract_Header, Calls.Order_No, Calls.Date_Received, Calls.Scheduled_Date_Time,    Clients.Co_Name, Clients.Post_Code,     LU_Call_Types.Call_Type_Description, LU_Call_Types.Type_Band,    LU_Call_Status.Call_Status_Description,    LU_Company_Types.Company_Type_DescriptionFROM    { oj (((Siclops_Dispense.dbo.Calls Calls INNER JOIN Siclops_Dispense.dbo.LU_Call_Types LU_Call_Types ON        Calls.Call_Type = LU_Call_Types.Call_Type_Code)     INNER JOIN Siclops_Dispense.dbo.LU_Call_Status LU_Call_Status ON        Calls.Last_Event_Status = LU_Call_Status.Call_Status_Code)     INNER JOIN Siclops_Dispense.dbo.Clients Clients ON        Calls.Link_to_Client = Clients.Client_Ref)     LEFT OUTER JOIN Siclops_Dispense.dbo.LU_Company_Types LU_Company_Types ON        Clients.Company_Type = LU_Company_Types.Company_Type_Code}WHERE    Calls.Link_to_Contract_Header = '".$row_rs_member['companyident']."' AND    (LU_Call_Types.Type_Band = 'Project' OR    LU_Call_Types.Type_Band = 'Project Complete' OR    LU_Call_Types.Type_Band = 'Project Invoiced') AND    (LU_Call_Status.Call_Status_Description = 'Reported Done' OR    LU_Call_Status.Call_Status_Description = 'PTF Rep Done' OR    LU_Call_Status.Call_Status_Description = 'Proforma Sent' OR    LU_Call_Status.Call_Status_Description = 'Paperwork Recvd' OR    LU_Call_Status.Call_Status_Description = 'In Query' OR    LU_Call_Status.Call_Status_Description = 'Cryo PW Sent' OR    LU_Call_Status.Call_Status_Description = 'Complete' OR    LU_Call_Status.Call_Status_Description = 'Awaiting P/work' OR    LU_Call_Status.Call_Status_Description = 'Awaiting Invoic' OR    LU_Call_Status.Call_Status_Description = 'Await TB Return' OR    LU_Call_Status.Call_Status_Description = 'ApplicationSent')GROUP BY       LU_Call_Status.Call_Status_Description"; 

 

Now before the group by the query works fine.  As soon as i write it in the query fails completely.  I have also tried selecting distinct and unique on the same field but each time I write them in it fails.  I believe this maybe because this is an MS SQL query and as such I may have the syntax wrong.  Any ideas?

 

Thanks guys

[Barand]

GROUP BY call_status_description will give one row for each value of the description. That's what group by does. It is used for calculating totals etc.

 

You probably want ORDER BY

[fife]

Hi Barand

 

the call status description looks as follows

 

 

call1

call1

call1

call1

call2

call2

call3

call4

call4

 

I just want 

call1

call2

call3

call4

 

I thought using group by would sort this?  I have also tried DISTINCT on this field but that does not work either

[Barand]

What about the rest of the columns that you are selecting? Don't you want to show those?

[fife]

Yes I still want to show those.  I have just tried this but it also fails

 

 

 SELECT DISTINCT ON (LU_Call_Status.Call_Status_Description) ,    Calls.Call_Ref, Calls.Link_to_Contract_Header, Calls.Order_No, Calls.Date_Received, Calls.Scheduled_Date_Time,    Clients.Co_Name, Clients.Post_Code,      LU_Call_Types.Type_Band,    LU_Call_Status.Call_Status_Description,    LU_Company_Types.Company_Type_DescriptionFROM    { oj (((Siclops_Dispense.dbo.Calls Calls INNER JOIN Siclops_Dispense.dbo.LU_Call_Types LU_Call_Types ON        Calls.Call_Type = LU_Call_Types.Call_Type_Code)     INNER JOIN Siclops_Dispense.dbo.LU_Call_Status LU_Call_Status ON        Calls.Last_Event_Status = LU_Call_Status.Call_Status_Code)     INNER JOIN Siclops_Dispense.dbo.Clients Clients ON        Calls.Link_to_Client = Clients.Client_Ref)     LEFT OUTER JOIN Siclops_Dispense.dbo.LU_Company_Types LU_Company_Types ON        Clients.Company_Type = LU_Company_Types.Company_Type_Code} WHERE    Calls.Link_to_Contract_Header = '".$row_rs_member['companyident']."' AND    (LU_Call_Types.Type_Band = 'Project' OR    LU_Call_Types.Type_Band = 'Project Complete' OR    LU_Call_Types.Type_Band = 'Project Invoiced') AND    (LU_Call_Status.Call_Status_Description = 'Reported Done' OR    LU_Call_Status.Call_Status_Description = 'PTF Rep Done' OR    LU_Call_Status.Call_Status_Description = 'Proforma Sent' OR    LU_Call_Status.Call_Status_Description = 'Paperwork Recvd' OR    LU_Call_Status.Call_Status_Description = 'In Query' OR    LU_Call_Status.Call_Status_Description = 'Cryo PW Sent' OR    LU_Call_Status.Call_Status_Description = 'Complete' OR    LU_Call_Status.Call_Status_Description = 'Awaiting P/work' OR    LU_Call_Status.Call_Status_Description = 'Awaiting Invoic' OR    LU_Call_Status.Call_Status_Description = 'Await TB Return' OR    LU_Call_Status.Call_Status_Description = 'ApplicationSent')

[Barand]

With a GROUP BY you get one row per value, so you will only see one row of the other columns for each description

[fife]

Yes thats exactly what I want.  The field was named poorly previously and is actually names of pubs.  The table pulls back each time an engineer went to a pub.  I only want to show each pub once and when you click it you then get all the other calls.  So on the page im only echoing "Call_Status_Description" but the othe fields need to be there for the query to work properly.

[Barand]

Use order by and only print the name when its value changes

[fife]

thanks Barand Ill give that a try 

[fife]

Thanks guys that solved it.  Sort of.  It ruins my record set paging as paging thinks theres still 700 records when theres only 12 unique but its fine.  Here is my new code from barand

 

 
 SELECT    Calls.Call_Ref, Calls.Link_to_Contract_Header, Calls.Order_No, Calls.Date_Received, Calls.Scheduled_Date_Time,    Clients.Co_Name, Clients.Post_Code,    LU_Call_Types.Call_Type_Description, LU_Call_Types.Type_Band,    LU_Call_Status.Call_Status_Description,    LU_Company_Types.Company_Type_DescriptionFROM    { oj (((Siclops_Dispense.dbo.Calls Calls INNER JOIN Siclops_Dispense.dbo.LU_Call_Types LU_Call_Types ON        Calls.Call_Type = LU_Call_Types.Call_Type_Code)     INNER JOIN Siclops_Dispense.dbo.LU_Call_Status LU_Call_Status ON        Calls.Last_Event_Status = LU_Call_Status.Call_Status_Code)     INNER JOIN Siclops_Dispense.dbo.Clients Clients ON        Calls.Link_to_Client = Clients.Client_Ref)     LEFT OUTER JOIN Siclops_Dispense.dbo.LU_Company_Types LU_Company_Types ON        Clients.Company_Type = LU_Company_Types.Company_Type_Code} WHERE    Calls.Link_to_Contract_Header = '".$row_rs_member['companyident']."' AND    (LU_Call_Types.Type_Band = 'Project' OR    LU_Call_Types.Type_Band = 'Project Complete' OR    LU_Call_Types.Type_Band = 'Project Invoiced') AND    (LU_Call_Status.Call_Status_Description = 'Reported Done' OR    LU_Call_Status.Call_Status_Description = 'PTF Rep Done' OR    LU_Call_Status.Call_Status_Description = 'Proforma Sent' OR    LU_Call_Status.Call_Status_Description = 'Paperwork Recvd' OR    LU_Call_Status.Call_Status_Description = 'In Query' OR    LU_Call_Status.Call_Status_Description = 'Cryo PW Sent' OR    LU_Call_Status.Call_Status_Description = 'Complete' OR    LU_Call_Status.Call_Status_Description = 'Awaiting P/work' OR    LU_Call_Status.Call_Status_Description = 'Awaiting Invoic' OR    LU_Call_Status.Call_Status_Description = 'Await TB Return' OR    LU_Call_Status.Call_Status_Description = 'ApplicationSent') ORDER BY  LU_Call_Types.Call_Type_Description ASC, Calls.Scheduled_Date_Time DESC
 

 

Then in the if statement you just check if field is unique.