Log Parser Studio: Counts of 500 errors and non-errors by month

Question

Parsing w3c logs, I want to get the count of 500 errors and not for each month. This gives the count of 500s per month:

SELECT TO_STRING(date, 'yyyy-MM') AS yearMonth, COUNT(*) AS HowMany
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%pageIcareabout%'
AND sc-status = 500
GROUP BY yearMonth

Changing sc-status = 500 to sc-status <> 500 gives the count of not-500s. However, I don't know how to show both 500s and not-500s, as separate columns for each month.

Answer 1

I ended up with this:

SELECT 
      TO_STRING(date, 'yyyy-MM') AS yearMonth 
    , SUM(500) AS 500s
    , SUM(Not500) AS Not500s
USING 
      CASE sc-status WHEN 500 THEN 1 ELSE 0 END AS 500
    , CASE sc-status WHEN 500 THEN 0 ELSE 1 END AS Not500
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%pageIcareabout%'
GROUP BY yearMonth

Result is just what I wanted -- 3 columns, yearMonth, 500s, and Not500s, the last 2 being the count of their respective values for the month.