Count rows once where value appeatrs in one or more columns

Database
Enthusiast

Count rows once where value appeatrs in one or more columns

I need a little help with this, I'm sure it's fairly basic but I'm a bit stumped.

I have a derived table that looks like the following:

Key     Col1    Col2    Col3

1        user1

2                   user2   user2

3                               user3

4                 user1    user1

5       user3              user3

What I need to do is count rows where a given username value appears in that row in one or more columns.

7 REPLIES
Senior Apprentice

Re: Count rows once where value appeatrs in one or more columns

If there are only three columns you can use a brute force method:

select count(case when col1 = col2 or col1 = col3 or col2 = col3 then 1 end)
from (your derived table) as dt

Dieter

Enthusiast

Re: Count rows once where value appeatrs in one or more columns

Try this SQL

SEL COUNT(COALESCE(COL1, COL2, COL3))
FROM T

Enthusiast

Re: Count rows once where value appeatrs in one or more columns

This is very close, but the answer set I need should be a count by user as in

User    Total

User1  300

User2  225

User3  180

The users id can be in any of the columns, any combination.

Enthusiast

Re: Count rows once where value appeatrs in one or more columns

Here you go...

SEL COALESCE(COL1, COL2, COL3) as _USER, count(*)
FROM T
WHERE _USER IS NOT NULL
GROUP BY 1

Senior Apprentice

Re: Count rows once where value appeatrs in one or more columns

Sorry, i didn't understand correctly what you wanted.

Doa cross join to a table with three rows in it to split the row in three:

SELECT
CASE
WHEN t.x = 1 THEN col1
WHEN t.x = 2 THEN col2
WHEN t.x = 3 THEN col3
END,
COUNT(*)
FROM
(your derived table) AS dt CROSS JOIN
(SELECT ROW_NUMBER() OVER (ORDER BY InfoKey) AS x FROM dbc.dbcinfo) AS t
GROUP BY 1

Dieter

Re: Count rows once where value appeatrs in one or more columns

I'm trying to count rows in a table events where a date in column EventDate occurs between two dates given in another table customers.

CUSTOMERS

ID  EventFrom   EventTo
-- ---------- -----------
1 2011-01-01 2012-01-01
2 2012-12-10 2013-12-10
3 2010-05-01 2011-05-01
4 2011-01-01 2012-01-01
5 2012-07-30 2013-07-30
6 2011-06-21 2012-06-21
7 2011-06-22 2012-06-22
8 2010-02-19 2011-02-19

EVENTS

ID  EventDate
-- ----------
2 1999-01-01
2 2012-12-12
2 2012-12-13
3 1900-01-12
4 2011-02-10
4 2011-02-11
4 2011-02-12

RESULT

ID  EventFrom   EventTo      Events
-- ---------- ----------- ------
1 2011-01-01 2012-01-01 0
2 2012-12-10 2013-12-10 2
3 2010-05-01 2011-05-01 0
4 2011-01-01 2012-01-01 3
5 2012-07-30 2013-07-30 0
6 2011-06-21 2012-06-21 0
7 2011-06-22 2012-06-22 0
8 2010-02-19 2011-02-19 0

Senior Apprentice

Re: Count rows once where value appeatrs in one or more columns

You need an outer join using BETWEEN:

select c.id, c.EventFrom, c.EventTo, count(e.EventDate)
from customers as c
left join Events as e
on e.ID = c.ID
and e.EventDate between c.EventFrom and c.EventTo
group by 1,2,3