CODEWITHSUNDEEP
sql
Manoj I
Manoj I

Reputation: 1099

Get the distinct count of values from a table with multiple where clauses

My table structure is this

id   last_mod_dt     nr     is_u     is_rog     is_ror    is_unv
1       x            uuid1   1         1          1         0
2       y            uuid1   1         0          1         1
3       z            uuid2   1         1          1         1

I want the count of rows with:

  1. is_ror=1 or is_rog =1
  2. is_u=1
  3. is_unv=1

All in a single query. Is it possible?

The problem I am facing is that there can be same values for nr as is the case in the table above.

Upvotes: 0

Views: 130

Answers (4)

mkk
mkk

Reputation: 7693

how about something like

SELECT
    SUM(IF(is_u > 0 AND is_rog > 0, 1, 0)) AS count_something,
    ... 
from table
group by nr

I think it will do the trick

I am of course not sure what you want exactly, but I believe you can use the logic to produce your desired result.

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

Sounds pretty simple if "all in a single query" does not disqualify subselects;

SELECT 
  (SELECT COUNT(DISTINCT nr) FROM table1 WHERE is_ror=1 OR is_rog=1) cnt_ror_reg,
  (SELECT COUNT(DISTINCT nr) FROM table1 WHERE is_u=1) cnt_u,
  (SELECT COUNT(DISTINCT nr) FROM table1 WHERE is_unv=1) cnt_unv;

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40309

Case statments provide mondo flexibility...

SELECT
  sum(case
        when is_ror = 1 or is_rog = 1 then 1
        else 0
      end) FirstCount
 ,sum(case
        when is_u = 1 then 1
        else 0
      end) SecondCount
 ,sum(case
        when is_unv = 1 then 1
        else 0
      end) ThirdCount
 from MyTable

Upvotes: 1

Matt T
Matt T

Reputation: 617

you can use union to get multiple results e.g.

select count(*) from table with is_ror=1 or is_rog =1 union select count(*) from table with is_u=1 union select count(*) from table with is_unv=1

Then the result set will contain three rows each with one of the counts.

Upvotes: 0

Related Questions