Counting SQL entries

Question

I'm having a hard time counting entries in SQL database. I need to count all entries of the same value in a row. Counting entries in a column down I got no problem with but can't seem to get the row count right. Please can someone help?

Name	Day1	Day2	Day3
Name 1	D	D	N
Name 2	D	N	N
Name 3	D	D	D
Name 4	N	D	D

Example:

Select count(Day1) as [Day Shift] 
from MyTable 
where Day1 = 'D'

Result: 3

I need to count Name1 D's. Result must be Name1 = 2.

Pleas can someone help?

Thank you in advance.

Mel

This SQL:

select count(case when Day1 = 'D' then 1 else null end) as [Off Days] 
from mYtABLE

still returns the same result.

Aggregate functions only work on columns, not on rows. And you're only translating D values in Day1 column to 1, so you're getting 3 because there are 3 Ds in Day1 column. — Kurt, May 15 '23 at 22:44
you need do do it like `case day1 when 'D' then 1 else 0 end + case day2 when 'D' then 1 else 0 end + case day3 when 'D' then 1 else 0 end as d_count` — Kurt, May 15 '23 at 22:45
Sample data is great, but you also need to _specify_ the expected result. — jarlh, May 16 '23 at 07:10

Sawyer Watts · Answer 1 · 2023-05-15T23:32:03.663

0

Since your problem only describes wanting to inspect the number of a single, specific value on a table with very few columns, here's a slightly hacky approach.

DECLARE @vCode CHAR(1) = 'D'

SELECT 
    Name,
      IIF(Day1 = @vCode, 1, 0)
    + IIF(Day2 = @vCode, 1, 0)
    + IIF(Day3 = @vCode, 1, 0)
    AS Shift
FROM mYtABLE

If you need to do this for a table with more that just a handful of columns as described, and/or if you need to do this for more than just a single/specific value, I would check out cursors.

EDIT: I added the @vCode variable so you can change that value to D, N, etc as needed to change the query.

edited May 15 '23 at 23:32

answered May 15 '23 at 22:43

Sawyer Watts

9
2

Hi Sawyer, Thank you so much! You make it look so easy. Won't forget that again! Thank you again. – Melger May 15 '23 at 22:52
Now if I need to count the N's as Night Shift? how do I change value but for same column? – Melger May 15 '23 at 23:00
Product specific answer to a question with no dbms specified. At least tell us which dbms this is for. – jarlh May 16 '23 at 07:08

score 0 · Answer 2 · answered May 15 '23 at 22:52

You can do it using the conditional aggregation :

select Name, max(Day1) as Day1,  max(Day2) as Day2,  max(Day3) as Day3,
  COUNT(case when Day1='D' then 1 end) +
  COUNT(case when Day2='D' then 1 end) +
  COUNT(case when Day3='D' then 1 end)
  as number_of_D
from mytable
group by Name

Result :

Name    Day1    Day2    Day3    number_of_D
Name1   D       D       N       2
Name2   D       N       N       1
Name3   D       D       D       3
Name4   N       D       D       2

Demo here

This solution is working on mysql, postgresql and many others

score 0 · Answer 3 · answered May 15 '23 at 23:22

Seeing and appreciating @SelVazi's answer, however I don't quite understand the need for GROUP BY and the max() function. This seems to work just as well, and it's simpler:

select name, 
(case when day1 = 'D' then 1 else 0 end) +
(case when day2 = 'D' then 1 else 0 end) +
(case when day3 = 'D' then 1 else 0 end)
from mytable;

Counting SQL entries

3 Answers3