CODEWITHSUNDEEP
sql
Programmer
Programmer

Reputation: 129

Check if more than 1% of records in a table are error?

i have a table say, Table1 and one more table say, ErrorTable1. ErrorTable1 is having all error records from Table1 found after applying validation on each field. can anybody tell me how do i check are more than 1% of Table1 records are errors?

Upvotes: 0

Views: 112

Answers (2)

gbn
gbn

Reputation: 432491

SELECT
   'More than 1% fail', 100.0 * E.ErrCnt / T.RowCnt AS ErrorPercent
FROM
   (SELECT COUNT(*) AS ErrCnt FROM ErrorTable1) E
   CROSS JOIN
   (SELECT COUNT(*) AS RowCnt FROM Table1) T
WHERE -- can use 1.0 ... > 0.01 too
   100.0 * E.ErrCnt / T.RowCnt > 1

Change COUNT(DISTINCT keycolumn) if each row in table1 can have multiple errors in ErrorTable1

Upvotes: 1

David
David

Reputation: 219016

This should give you the percentage, with some assumptions about your data:

SELECT CAST((SELECT COUNT(DISTINCT Table1ID) FROM ErrorTable1) AS DECIMAL) / CAST(COUNT(ID) AS DECIMAL) FROM Table1

It's basically looking for any single instance of a reference to a record in Table1 that exists in ErrorTable1, ignoring the test. If one or more records in ErrorTable1 point to a record in Table1, it counts it as a single "error record" for the purpose of the percentage.

Edit: I just thought of something else... If all you care about is if the percentage is greater than or equal to 1 then you probably don't need the casting operations. You'll want to test it to be sure, but if it's just dropping the remainder on integer division (I'm not 100% sure) then you may be able to just leave them as integers and check for a positive result.

Upvotes: 1

Related Questions