Multiple selects on same column, same table, one query

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 locationYou 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" <turnbull.jack@.ntlworld.com> wrote in message
news:FpGcb.208$%f1.164@.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|||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|||Nice! :-)

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:l7Cdncag0qX-3e6iRVn-ug@.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|||Thanks Shervin,
Works exactly as I need, I am indebted!
Cheers,
Jack
"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:vn6fu8bgg3qt64@.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" <turnbull.jack@.ntlworld.com> wrote in message
> news:FpGcb.208$%f1.164@.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|||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" <turnbull.jack@.ntlworld.com> wrote in message
news:vpHcb.266$%f1.56@.newsfep1-gui.server.ntli.net...
> Thanks Shervin,
> Works exactly as I need, I am indebted!
> Cheers,
> Jack
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
> news:vn6fu8bgg3qt64@.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" <turnbull.jack@.ntlworld.com> wrote in message
> > news:FpGcb.208$%f1.164@.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
> >|||Wow!
Cheers,
Jack

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:l7Cdncag0qX-3e6iRVn-ug@.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