CODEWITHSUNDEEP
sql
ASKI
ASKI

Reputation: 13

SQL Count Distinct IDs with condition

I would like to make a query in which I can count the number of IDs which have at least 1 true value for a given column, and do this for multiple columns at once.

For example, I have a database like this:

Table1    
Name    col_1    col_2
A       true     true
A       false    true
B       false    false
C       true     false
C       true     false

Table2    
Name    ID
A       1
B       2
C       3

I essentially would like to count how many IDs have a certain column true (the resulting output appearing as follows):

Row    col_1_true    col_2_true
1      2             1

For a single column, I can do:

SELECT 
  COUNT(DISTINCT ID, col_1) as col_1_true
FROM table1, table2
  WHERE table1.Name = table2.Name
        col_1 = true

But I want to get all desired counts in my final output from a single query (as my actual dataset has many columns I would like to query on a regular basis), so something like the following: 

SELECT 
  COUNT(DISTINCT ID, col_1 = true) as col_1_true
  COUNT(DISTINCT ID, col_2 = true) as col_2_true
FROM table1, table2
  WHERE table1.Name = table2.Name

I've tried a number of methods, which are along the lines of:

SUM(DISTINCT ID, CASE WHEN col_1 = true THEN 1 ELSE 0 END)

which throws and error (too many arguments for function SUM), or

SELECT
  SUM(
    CASE WHEN col_1 = true
    THEN 1 ELSE 0 END)
  AS col_1_true,

  SUM(
    CASE WHEN col_2 = true
    THEN 1 ELSE 0 END)
  AS col_2_true

FROM table1, table2
  WHERE table1.Name = table2.Name
  GROUP BY table2.ID

Which does not provide the proper output. It's output is:

Row    col_1_true    col_2_true
1      1             1
2      0             1
3      0             0
4      1             0
5      1             0

I'm thinking I may have to bring in subqueries and/or subtables, but am unsure how to proceed.

Upvotes: 0

Views: 1272

Answers (1)

Kostya
Kostya

Reputation: 1605

in tsql it would be something like:

SELECT
  COUNT(DISTINCT 
    CASE WHEN col_1 = true
    THEN table2.ID END)
  AS col_1_true,

  COUNT(DISTINCT 
    CASE WHEN col_2 = true
    THEN table2.ID END)
  AS col_2_true

FROM table1, table2
  WHERE table1.Name = table2.Name

Upvotes: 2

Related Questions