Conditional SQL Count

Conditional Count on a field

I think you may be after

select 
    jobID, JobName,
    sum(case when Priority = 1 then 1 else 0 end) as priority1,
    sum(case when Priority = 2 then 1 else 0 end) as priority2,
    sum(case when Priority = 3 then 1 else 0 end) as priority3,
    sum(case when Priority = 4 then 1 else 0 end) as priority4,
    sum(case when Priority = 5 then 1 else 0 end) as priority5
from
    Jobs
group by 
    jobID, JobName

However I am uncertain if you need to the jobID and JobName in your results if so remove them and remove the group by,

Is it possible to specify condition in Count()?

If you can't just limit the query itself with a where clause, you can use the fact that the count aggregate only counts the non-null values:

select count(case Position when 'Manager' then 1 else null end)
from ...

You can also use the sum aggregate in a similar way:

select sum(case Position when 'Manager' then 1 else 0 end)
from ...

Get conditional count and conditional DISTINCT count in a single SELECT

Use the aggregate FILTER clause. Then you can combine your count with DISTINCT:

SELECT s.logged_on::date AS login_date
     , count(*)                FILTER (WHERE s.device = 'mobile') AS mobile_count
     , count(DISTINCT user_id) FILTER (WHERE s.device = 'web') AS web_count
FROM   session_log s
JOIN   standard_users su USING (user_id)
GROUP  BY login_date;

See:

• Aggregate columns with additional (distinct) filters

I also simplified your twisted formulation with LEFT JOIN and then IS NOT NULL. Boils down to a plain JOIN.

If referential integrity between session_log.user_id and standard_users.user_id is enforced with a FK constraint, and standard_users.user_id is defined UNIQUE or PK - as seems reasonable - you can drop the JOIN completely:

SELECT logged_on::date AS login_date
     , count(*)                FILTER (WHERE device = 'mobile') AS mobile_count
     , count(DISTINCT user_id) FILTER (WHERE device = 'web') AS web_count
FROM   session_log
GROUP  BY 1;

SQL Pivot on Conditional Count

Conditional aggregation is simpler:

select vulnerability,
       sum(case when week = 1 then 1 else 0 end) as week_1,
       sum(case when week = 2 then 1 else 0 end) as week_2,
       sum(case when week = 3 then 1 else 0 end) as week_3
from t
group by vulnerability;

Note only is pivot bespoke syntax, but it is sensitive to what columns are in the table. Extra columns are interpreted as "group by" criteria, affecting the results from the query.

Conditional COUNT within CASE statement

You were nearly there! I've made a few changes:

SELECT 
  DATEFROMPARTS(YEAR(t1.Date), MONTH(t1.Date),1) AS Draft_Date, 
  Membership,
  COUNT(CASE WHEN t1.Membership = 5 AND t1.Service_Type = 'A' THEN 1 END) as m5stA,
  COUNT(CASE WHEN t1.Membership = 2 AND t1.Service_Type IN ('J','C') THEN 1 END) as m2stJC
FROM Table1 t1 
GROUP BY YEAR(t1.Date), MONTH(t1.Date), Membership

Changes:

• Avoid using apostrophes to alias column names, use ascii standard " double quotes if you must
• When doing a conditional count, put the count outside the CASE WHEN, and have the case when return something (any non null thing will be fine - i used 1, but it could also have been 'x' etc) when the condition is met. Don't put an ELSE - CASE WHEN will return null if there is no ELSE and the condition is not met, and nulls don't COUNT (you could also write ELSE NULL, though it's redundant)
• Qualify all your column names, always - this helps keep the query working when more tables are added in future, or even if new columns with the same names are added to existing tables
• You forgot a THEN in the second WHEN
• You don't necessarily need to GROUP BY the output of DATEFROMPARTS. When a deterministic function is used (always produces the same output from the same inputs) the db is smart enough to know that grouping on the inputs is also fine
• Your example data didn't contain any data that would make the COUNT count 1+ by the way, but I'm sure you will have other conditional counts that work out (it just made it harder to test)

Conditional count using CASE WHEN

I think I understand your question correctly, if not let me know.

You can use a windowed function to accomplish this:

SELECT
ITEM,
COUNT(DISTINCT CID) OVER (PARTITION BY ITEM) AS UNIQ_CNT_OF_CID
FROM T

This will give you the same number of rows, so you if you want the unique from this, you can use a CTE or sub-query it like so:

SELECT DISTINCT
ITEM, UNIQ_CNT_OF_CID
FROM
    (
    SELECT
    ITEM,
    COUNT(DISTINCT CID) OVER (PARTITION BY ITEM) AS UNIQ_CNT_OF_CID
    FROM T
    ) final

Sql conditional count with join

The main problem that I see is that you're joining in both tables together, which means that you're going to effectively be multiplying out by both numbers, which is why your counts aren't going to be correct. For example, if the Newsitem has been read 3 times by the user and rated by 8 users then you're going to end up getting 24 rows, so it will look like it has been rated 24 times. You can add a DISTINCT to your COUNT of the ratings IDs and that should correct that issue. Average should be unaffected because the average of 1 and 2 is the same as the average of 1, 1, 2, & 2 (for example).

You can then handle the reads by adding the userid to the JOIN condition (since it's an OUTER JOIN it shouldn't cause any loss of results) instead of in a CASE statement for your COUNT, then you can do a COUNT on distinct id values from Usernews. The resulting query would be:

SELECT
    I.guid,
    I.supplier,
    COUNT(DISTINCT R.id) AS number_of_ratings,
    AVG(R.rating) AS avg_rating,
    COUNT(DISTINCT UN.id) AS number_of_reads
FROM
    NewsItem I
LEFT OUTER JOIN NewsRating R ON R.newsguid = I.guid
LEFT OUTER JOIN UserNews UN ON
    UN.newsguid = I.guid AND
    UN.userid = @userid
GROUP BY
    I.guid,
    I.supplier

While that should work, you might get better results from a subquery, as the above needs to explode out the results and then aggregate them, perhaps unnecessarily. Also, some people might find the below to be a little clearer.

SELECT
    I.guid,
    I.supplier,
    R.number_of_ratings,
    R.avg_rating,
    COUNT(*) AS number_of_reads
FROM
    NewsItem I
LEFT OUTER JOIN
(
    SELECT
        newsguid,
        COUNT(*) AS number_of_ratings,
        AVG(rating) AS avg_rating
    FROM
        NewsRating
    GROUP BY
        newsguid
) R ON R.newsguid = I.guid
LEFT OUTER JOIN UserNews UN ON UN.newsguid = I.guid AND UN.userid = @userid
GROUP BY
    I.guid,
    I.supplier,
    R.number_of_ratings,
    R.avg_rating

SQL count distinct with a condition based on a different column

You need a distinct count of exams, so I think that is:

select candidate, qualification, 
       count(distinct units) as total_units,
       count(distinct case when exam_status = 'Passed' then exam end)
from example_table
group by candidate, qualification;

If you want to sum the units of the passed exams, this becomes trickier. I would recommend window functions:

select candidate, qualification, count(distinct unit),
       sum(case when exam_status = 'Passed' and seqnum = 1 then unit end) as total_units,
       count(distinct case when exam_status = 'Passed' then exam end)
from (select et.*,
             row_number() over (partition by candidate, qualification, exam 
                                order by (case when exam_status = 'Passed' then 1 else 2 end)
                               ) as seqnum
      from example_table et
     ) et
where seqnum = 1
group by candidate, qualification;

A Better Way for Conditional Counting?

Easiest is just as @jarlh mentions and use case/sum combinations to accomplish as follows.

SQL> select count(*) totalRows
  2    , sum(case when p.age=45 then 1 else 0 end) total45YO
  3    , sum(case when c.name like 'Chicago' then 1 else 0 end) totalChicago
  4    , count(distinct c.name) totalCities
  5  from person p
  6  inner join city c
  7    on p.cityID = c.ID
  8  inner join state s
  9    on c.stateID = s.ID;
TOTALROWS    TOTAL45YO    TOTALCHICAGO    TOTALCITIES
____________ ____________ _______________ ______________
           5            2               3              3

SQL>

---

Related Topics