Multiple selects on same column, same table, one query

[JackT]

I want to get a column count several times in one query using different
filters but can't work out how to do it - can anyone point me in the right
direction?

For example, how would combine these two selects into one query that will
list the total and filtered actions:

SELECT COUNT(actions) as actioncount, location
FROM mytable
GROUP BY location

SELECT COUNT(actions) as actioncount, location
FROM mytable
WHERE mycondition IS NULL
GROUP BY location

[Shervin Shapourian]

You can use subqueries like this:

select location,
actioncount1 = (select count(actions)
from mytable T1
where T1.location = mytable.location
),
actioncount2 = (select count(actions)
from mytable T2
where mycondition is null
and T2.location = mytable.location
)
from mytable
group by location

Shervin

"JackT" <tu***********@ntlworld.com> wrote in message
news:Fp***************@newsfep1-gui.server.ntli.net...

I want to get a column count several times in one query using different
filters but can't work out how to do it - can anyone point me in the right
direction?

For example, how would combine these two selects into one query that will
list the total and filtered actions:

SELECT COUNT(actions) as actioncount, location
FROM mytable
GROUP BY location

SELECT COUNT(actions) as actioncount, location
FROM mytable
WHERE mycondition IS NULL
GROUP BY location

[David Portas]

SELECT COUNT(CASE WHEN mycondition IS NULL THEN actions END) AS
actioncount1,
COUNT(actions) AS actioncount2,
location
FROM mytable
GROUP BY location

--
David Portas
------------
Please reply only to the newsgroup

[Shervin Shapourian]

Nice! :-)

"David Portas" <RE****************************@acm.org> wrote in message
news:l7********************@giganews.com...

SELECT COUNT(CASE WHEN mycondition IS NULL THEN actions END) AS
actioncount1,
COUNT(actions) AS actioncount2,
location
FROM mytable
GROUP BY location

--
David Portas
------------
Please reply only to the newsgroup

[JackT]

Thanks Shervin,
Works exactly as I need, I am indebted!
Cheers,
Jack
"Shervin Shapourian" <Sh**********@hotmail.com> wrote in message
news:vn************@corp.supernews.com...

You can use subqueries like this:

select location,
actioncount1 = (select count(actions)
from mytable T1
where T1.location = mytable.location
),
actioncount2 = (select count(actions)
from mytable T2
where mycondition is null
and T2.location = mytable.location
)
from mytable
group by location

Shervin

"JackT" <tu***********@ntlworld.com> wrote in message
news:Fp***************@newsfep1-gui.server.ntli.net...

I want to get a column count several times in one query using different
filters but can't work out how to do it - can anyone point me in the right direction?

For example, how would combine these two selects into one query that will list the total and filtered actions:

SELECT COUNT(actions) as actioncount, location
FROM mytable
GROUP BY location

SELECT COUNT(actions) as actioncount, location
FROM mytable
WHERE mycondition IS NULL
GROUP BY location

[Shervin Shapourian]

You're welcome my friend, but take a look at David's code. I prefer his way
:-) Subqueries are not the most efficient way to do thing in most cases. But
they are so easy to use. They are for lazy guys like me ;-)

Shervin

"JackT" <tu***********@ntlworld.com> wrote in message
news:vp**************@newsfep1-gui.server.ntli.net...

Thanks Shervin,
Works exactly as I need, I am indebted!
Cheers,
Jack
"Shervin Shapourian" <Sh**********@hotmail.com> wrote in message
news:vn************@corp.supernews.com...

You can use subqueries like this:

select location,
actioncount1 = (select count(actions)
from mytable T1
where T1.location = mytable.location
),
actioncount2 = (select count(actions)
from mytable T2
where mycondition is null
and T2.location = mytable.location
)
from mytable
group by location

Shervin

"JackT" <tu***********@ntlworld.com> wrote in message
news:Fp***************@newsfep1-gui.server.ntli.net...

I want to get a column count several times in one query using different filters but can't work out how to do it - can anyone point me in the right direction?

For example, how would combine these two selects into one query that will list the total and filtered actions:

SELECT COUNT(actions) as actioncount, location
FROM mytable
GROUP BY location

SELECT COUNT(actions) as actioncount, location
FROM mytable
WHERE mycondition IS NULL
GROUP BY location

[JackT]

Wow!
Cheers,
Jack

"David Portas" <RE****************************@acm.org> wrote in message
news:l7********************@giganews.com...

SELECT COUNT(CASE WHEN mycondition IS NULL THEN actions END) AS
actioncount1,
COUNT(actions) AS actioncount2,
location
FROM mytable
GROUP BY location

--
David Portas
------------
Please reply only to the newsgroup