SQL : Count with "==" to check in SQL Server

Question

I'm working with SQL Server and I want to create a view (my_View) which has these columns :

[element_1]
[element_2]
[element_3]
[element_4]

All of them are referring to the same column, named [parent_1], in another table (AT)

[parent_1] can have 4 possible values [value_1], [value_2], [value_3] and [value_4].

What I want is that, using COUNT,

[element_1] is equal to the number of times that [parent_1] is equal to [value_1]
same for [element_2], [element_3] and [element_4] equals to [value_2], [value_3] and [value_4].

Is it possible to use "==" inside the COUNT to see if it checks the criteria?

Something like this:

COUNT (AT.parent_1 == "value_1") AS element_1
COUNT (AT.parent_1 == "value_2") AS element_2
COUNT (AT.parent_1 == "value_3") AS element_3
COUNT (AT.parent_1 == "value_4") AS element_4

Thanks guys

Yes, but `==` is not used in SQL Server, nor does it have a boolean type, what you would need to do is something like `COUNT(CASE WHEN AT.parent_1 = 'value_1' THEN 1 END) AS Element_1`, or perhaps more simply `SUM(IIF(AT.parent = 'value_1',1,0)` — GarethD, Feb 15 '22 at 15:14

Jérémie Leclercq · Accepted Answer · 2022-02-15T15:18:43.810

1

You can use the CASE instruction for that

https://learn.microsoft.com/fr-fr/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver15

SUM (CASE WHEN AT.parent_1 = 'value_4' THEN 1 ELSE 0 END) as element_4

edited Feb 15 '22 at 15:18

answered Feb 15 '22 at 15:15

Jérémie Leclercq

174
1
7

2

This either needs to use `SUM` instead of count or return `NULL` in the `ELSE` part (or omit else completely), as this will also count rows where it is false – GarethD Feb 15 '22 at 15:17
Thanks for your help, I will try that. – Rom1_LN Feb 15 '22 at 15:25
`COUNT` without an `ELSE` feels more obvious – Charlieface Feb 15 '22 at 16:51

Teja Goud Kandula · Answer 2 · 2022-02-15T15:22:56.467

0

create my_view as 
select 
-- other list of columns,
SUM (CASE WHEN AT.parent_1 = 'value_1' THEN 1 ELSE 0 END) as element_1,
SUM (CASE WHEN AT.parent_1 = 'value_2' THEN 1 ELSE 0 END) as element_2,
SUM (CASE WHEN AT.parent_1 = 'value_3' THEN 1 ELSE 0 END) as element_3,
SUM (CASE WHEN AT.parent_1 = 'value_4' THEN 1 ELSE 0 END) as element_4
from tableName AT

There is no need to use == like that of a programming language, in SQL comparison operator is =

edited Feb 15 '22 at 15:22

answered Feb 15 '22 at 15:16

Teja Goud Kandula

1,462
13
26

Same point as [Gareth made](https://stackoverflow.com/questions/71128823/sql-count-with-to-check-in-sql-server#comment125734482_71128878) on the other answer. This needs to be `SUM` or return `NULL` in the `ELSE` (or the `ELSE` completely omitted). – Thom A Feb 15 '22 at 15:22
Understood. Updated the query. – Teja Goud Kandula Feb 15 '22 at 15:23

score 0 · Answer 3 · answered Feb 15 '22 at 15:19

0

You can do something like:

SELECT
       sum(case when parent_1 = "value_1" then 1 else 0 end) as element_1,
       sum(case when parent_1 = "value_2" then 1 else 0 end) as element_2,
       sum(case when parent_1 = "value_3" then 1 else 0 end) as element_3,
       sum(case when parent_1 = "value_4" then 1 else 0 end) as element_4
FROM table;

answered Feb 15 '22 at 15:19

Schnurres

152
7

I can't follow you. I already use SUM and never used COUNT. Can you point at what is wrong with the query? – Schnurres Feb 16 '22 at 08:42

SQL : Count with "==" to check in SQL Server

3 Answers3