none
SubQuery in Group by Clause RRS feed

  • Question

  • Hi,

    I am trying to use a subquery in an odbc connection to connect to an MS Access database.

    My code is

    Select
        T1.ASSR_FEE as AssessorFee,
        T1.CAU_DT as Comment,
        T1.Incident_Date as IncidentDate,
        T1.Inv_amount as InvoiceAmount,
        T1.Rep_Cost as RepCost,
        t1.Est_cost_init as EstimatedCost,
        T2.CO_Name & ' ' & T2.Surname as DriverName,
        T3.CO_Name & ' ' & T3.Surname as InspectorName,
        MainDescription = (select Full_Desc from Sy_code_list t5 where t5.ID_Code = t4.ID_Code and t5.Level_2 = t4.Level_2 and t5.Level_3 = '0' or t5.Level_3 = '-1')
    FROM
        (((cl_details T1)
        LEFT OUTER JOIN [C:\Users\cjack\Desktop\Test Stuff\ecm_hol.mdb].tb_pers T2 on T1.pers_no = T2.ref_id)
        LEFT OUTER JOIN [C:\Users\cjack\Desktop\Test Stuff\ecm_hol.mdb].tb_pers T3 on T1.insp_id = T3.ref_id)
        LEFT OUTER JOIN cl_coding T4 on T1.cl_id = T4.cl_id
        
    Group By
        T1.ASSR_FEE,
        T1.CAU_DT,
        T1.Incident_Date,
        T1.Inv_amount,
        T1.Rep_Cost,
        t1.Est_cost_init,
        T2.CO_Name & ' ' & T2.Surname,
        T3.CO_Name & ' ' & T3.Surname
    How would I go about adding the query into the groupby clause?

    Wednesday, May 16, 2018 10:38 AM

Answers

  • Don't you mean

     (select Full_Desc from Sy_code_list t5 where t5.ID_Code = t4.ID_Code and t5.Level_2 = t4.Level_2 and t5.Level_3 = '0' or t5.Level_3 = '-1') AS MainDescription

    I would save the query without a GROUP BY clause, then create a new query based on that one, and make that a Totals query. You should be able to group by MainDescription.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JaK82 Wednesday, May 16, 2018 12:02 PM
    Wednesday, May 16, 2018 11:21 AM

All replies

  • Don't you mean

     (select Full_Desc from Sy_code_list t5 where t5.ID_Code = t4.ID_Code and t5.Level_2 = t4.Level_2 and t5.Level_3 = '0' or t5.Level_3 = '-1') AS MainDescription

    I would save the query without a GROUP BY clause, then create a new query based on that one, and make that a Totals query. You should be able to group by MainDescription.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JaK82 Wednesday, May 16, 2018 12:02 PM
    Wednesday, May 16, 2018 11:21 AM
  • Thanks Han,  It is failing due to returning multiple rows, is my OR in the right place

    (select Full_Desc from Sy_code_list t5 where t5.ID_Code = t4.ID_Code and t5.Level_2 = t4.Level_2 and t5.Level_3 = '0' or t5.Level_3 = '-1')

    Where it would return all of them if it was t5.Level_3 = '-1'  I am only wanting that or to apply to t5.level3

    Its ok figured out I needed brackets

    Thanks

    Chris



    • Edited by JaK82 Wednesday, May 16, 2018 12:02 PM
    Wednesday, May 16, 2018 11:59 AM