Subquery Totals in Access-CodePudding

I need to breakdown the two subqueries by File_Date. Now it's just giving me the total number of records per Service_Code.

SELECT
            [Request File].File_Date,
            Count([Request File].SSN) AS [Borrower Count],
            Sum([Request File].Discharge_Amt) AS Total_Discharge_Amt,
              (SELECT Count([Request File].SSN) FROM [Request File] WHERE [Request File].Servicer_Code="500") AS 500_Count,
              (SELECT Count([Request File].SSN) FROM [Request File] WHERE [Request File].Servicer_Code="579") AS 579_Count
FROM [Request File]
GROUP BY [Request File].File_Date;

If I try and Group By File_Date for the two subqueries, I get an error "At most one record can be returned by this subquery".

SELECT
            [Request File].File_Date,
            Count([Request File].SSN) AS [Borrower Count],
            Sum([Request File].Discharge_Amt) AS Total_Discharge_Amt,
              (SELECT Count([Request File].SSN) FROM [Request File] WHERE [Request File].Servicer_Code="500" GROUP BY [Request File].File_Date) AS 500_Count,
              (SELECT Count([Request File].SSN) FROM [Request File] WHERE [Request File].Servicer_Code="579" GROUP BY [Request File].File_Date) AS 579_Count
FROM [Request File]
GROUP BY [Request File].File_Date;

CodePudding user response：

In the SELECT-list sub-queries must return one column and one record, because this value will be inserted in one result field.

Since all the data comes from the same table, no sub-query is required. Instead, we use the trick to conditionally count the records by summing up 1 when the condition is fulfilled and 0 otherwise:

SELECT
    File_Date,
    Count(SSN) AS [Borrower Count],
    Sum(Discharge_Amt) AS Total_Discharge_Amt,
    Sum(IIf(Servicer_Code="500", 1, 0)) AS 500_Count,
    Sum(IIf(Servicer_Code="579", 1, 0)) AS 579_Count
FROM [Request File]
GROUP BY File_Date;