Group By with Subquery Question

Question

Group By with Subquery Question

pnewhart

SSC Eights!

Points: 983
More actions
January 8, 2004 at 2:57 pm

#106725

--Basically, I need to have the number of claims audited every day, with the --number of errors for a particular error type (from the audit_errors table).

SELECT audit_date, audit_id, SUM(audit_total_units) AS totalclaims,
                   (SELECT SUM(error_unit_count)
                    FROM   audit_errors
                    WHERE audit_errors.audit_id = audits.audit_id AND audit_errors.error_type_id = 6) AS totalerrors
FROM Audits
GROUP BY audit_date, audit_id
order by audit_date
Returns multiple lines for the same day due to the requirement of passing the audit_id column to the subquery.
Table Structures:
Audits -
audit_id
audit_date
audit_total_units
Audit_errors -
error_id
audit_id
error_type_id
form_type_id
error_unit_count
description
Is this possible to do without a temporary table to accumulate the results of the query and then do a second grouping query?
Thanks in advance.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

SJTerrill SSCrazy Points: 2938 More actions · Answer 1

Shooting from the hip, something like this?

SELECTaudit_date,
audit_id,
Sum(audit_total_units) as totalclaims,
Sum(error_unit_count)
FROMaudits
JOIN
audit_errors
ON audits.audit_id = audit_errors.audit_id
WHEREaudit_errors.error_type_id = 6
GROUP BYaudit_date,
audit_id


--SJTerrill

Jonathan SSC-Insane Points: 20427 More actions · Answer 2

Can't you just join the tables?

SELECT a.Audit_Date, SUM(a.Audit_Total_Units) AS TotalClaims, SUM(ISNULL(e.Error_Unit_Count,0)) AS TotalErrors

FROM Audits a LEFT JOIN Audit_Errors e ON a.Audit_Id = e.Audit_Id AND e.Error_Type_Id = 6

GROUP BY a.Audit_Date

ORDER BY a.Audit_Date

--Jonathan