Help needed with SQL count query

Data and Databases
1

Hey everyone! I’m trying to write a SQL query to count some data from a table. I want to get the number of successful entries, failed entries, and the total count for a specific date range. Here’s what I’ve come up with so far:

SELECT
  (SELECT COUNT(*) FROM CUSTOMER_DATA
   WHERE insert_date BETWEEN TO_DATE('2023-03-01', 'YYYY-MM-DD')
   AND TO_DATE('2023-03-05', 'YYYY-MM-DD')
   AND result_status = 'OK') AS Successful,
  (SELECT COUNT(*) FROM CUSTOMER_DATA
   WHERE insert_date BETWEEN TO_DATE('2023-03-01', 'YYYY-MM-DD')
   AND TO_DATE('2023-03-05', 'YYYY-MM-DD')
   AND result_status != 'OK') AS Failed,
  (SELECT COUNT(*) FROM CUSTOMER_DATA
   WHERE insert_date BETWEEN TO_DATE('2023-03-01', 'YYYY-MM-DD')
   AND TO_DATE('2023-03-05', 'YYYY-MM-DD')) AS TotalCount

Is this the right way to do it? I’m not sure if using subqueries like this is efficient. Any tips on how to improve this query would be great. Thanks!

2

Your approach is valid, but there’s room for optimization. Consider using a single SELECT statement with conditional aggregation for improved efficiency:

SELECT
COUNT(*) AS TotalCount,
COUNT(CASE WHEN result_status = ‘OK’ THEN 1 END) AS Successful,
COUNT(CASE WHEN result_status != ‘OK’ THEN 1 END) AS Failed
FROM CUSTOMER_DATA
WHERE insert_date BETWEEN TO_DATE(‘2023-03-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2023-03-05’, ‘YYYY-MM-DD’)

This method reduces the number of table scans to one, potentially enhancing query performance, especially with larger datasets. It’s also more concise and easier to maintain. Remember to create appropriate indexes on the insert_date and result_status columns for optimal execution.

3

hey swimingfish! ur query looks good, but have u tried using conditional aggregation? it might be more efficient. something like:

SELECT
SUM(CASE WHEN result_status = ‘OK’ THEN 1 ELSE 0 END) AS Successful,
SUM(CASE WHEN result_status != ‘OK’ THEN 1 ELSE 0 END) AS Failed,
COUNT(*) AS TotalCount
FROM CUSTOMER_DATA
WHERE insert_date BETWEEN TO_DATE(‘2023-03-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2023-03-05’, ‘YYYY-MM-DD’)

what do u think? curious to hear if this helps!

4

yo SwimmingFish, TalentedSculptor23’s idea is solid. another option is using GROUP BY with ROLLUP:

SELECT
COALESCE(result_status, ‘Total’) AS status,
COUNT(*) AS count
FROM CUSTOMER_DATA
WHERE insert_date BETWEEN TO_DATE(‘2023-03-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2023-03-05’, ‘YYYY-MM-DD’)
GROUP BY ROLLUP(result_status)

this gives u a breakdown + total in 1 go. might be worth a shot!