'SQL : Count with "==" to check in SQL Server [duplicate]

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

sqlsql-server


Solution 1:[1]

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;

Solution 2:[2]

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 =

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Schnurres
Solution 2

Related Questions