I've got this table and I wish to add columns that summarized it:

table now:

What I wish is to add columns that counts stuff in the table and give a value in each row:

Desired output:

Someone helped me with the last one (Total_PATS) with counting all row with:

 COUNT(*) OVER () AS [total]      << for all rows.

how to do it with conditions? I have 'X' so I want to count all the rows where has_T has an X...

CodePudding user response：

As you are storing blank values, or values of spaces, the COUNT will still count those valuesl COUNT counts non-NULL values. Ideally, you should be storing NULL, not '', ' ' (or even ' ') in such values, it makes COUNTing the values much easier.

You could, however, NULL the values in the COUNT:

SELECT name,
       pat_id,
       has_t,
       has_y,
       has_g,
       COUNT(NULLIF(has_t,'')) OVER() AS total_t,
       COUNT(NULLIF(has_y,'')) OVER() AS total_y,
       COUNT(NULLIF(has_g,'')) OVER() AS total_g,
       COUNT(*) OVER() AS total
FROM dbo.Yourtable; 

CodePudding user response：

We can use conditional count with CASE:

SELECT 
name, pat_id, has_t, has_y, has_g,
COUNT (CASE WHEN has_t = 'X' THEN 1 END) OVER() AS Total_T,
COUNT (CASE WHEN has_y = 'X' THEN 1 END) OVER() AS Total_Y,
COUNT (CASE WHEN has_g = 'X' THEN 1 END) OVER() AS Total_G,
COUNT (*) OVER() AS Total_PATS
FROM yourtable; 

This will count X values only.

As already said in comments, using NULL rather than spaces/empty strings would be much better, for example because COUNT ignores NULL, so we could simply write COUNT(has_t) etc.