get it. I have a table that looks something like
Name Activity
==== ======== Mike Arrest
John Victim
Fred Suspect
John Suspect
Mike Victim
John Victim
Mike Arrest
For graphing purposes, I'd like to end up with a view that looks like
Name Victim_Count Arrest_Count Suspect_Count
==== ============ ============ ============= Mike 1 2 0
John 2 0 1
Fred 0 0 1
I've tried things like select name, (select count(*) where activity = 'Victim') as Victim_Count group by name. Which doesn't work.
Can anyone point me in the right direction? I know there are fixed number of
Activity types (3 in this case).
Any help is much appreciated,
MikeSELECT [name],
COUNT(CASE activity WHEN 'victim' THEN 1 END) AS victim_count,
COUNT(CASE activity WHEN 'arrest' THEN 1 END) AS arrest_count,
COUNT(CASE activity WHEN 'suspect' THEN 1 END) AS suspect_count
FROM Sometable
GROUP BY [name]
--
David Portas
--
Please reply only to the newsgroup
--|||This is a multi-part message in MIME format.
--=_NextPart_000_0185_01C3CF8B.993AB0B0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Try:
select
Name
, sum (case when Activity = 'Victim' then 1 else 0 end) as Victim_Count
, sum (case when Activity = 'Arrest' then 1 else 0 end) as Arrest_Count
, sum (case when Activity = 'Suspect' then 1 else 0 end) as Suspect_Count
from
MyTable
group by
Name
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:Ofw7lO7zDHA.2456@.TK2MSFTNGP12.phx.gbl...
I've been trying to do this on and off for over a month, just can't seem to
get it. I have a table that looks something like
Name Activity
==== ========Mike Arrest
John Victim
Fred Suspect
John Suspect
Mike Victim
John Victim
Mike Arrest
For graphing purposes, I'd like to end up with a view that looks like
Name Victim_Count Arrest_Count Suspect_Count
==== ============ ============ =============Mike 1 2 0
John 2 0 1
Fred 0 0 1
I've tried things like select name, (select count(*) where activity ='Victim') as Victim_Count group by name. Which doesn't work.
Can anyone point me in the right direction? I know there are fixed number of
Activity types (3 in this case).
Any help is much appreciated,
Mike
--=_NextPart_000_0185_01C3CF8B.993AB0B0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Try:
select
=Name
, sum (case =when Activity =3D 'Victim' then 1 else 0 end) as Victim_Count
, sum =(case when Activity =3D 'Arrest' then 1 else 0 end) as Arrest_Count
, sum (case =when Activity =3D 'Suspect' then 1 else 0 end) as Suspect_Count
from
MyTable
group by
Name
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Mike Hildner"
--=_NextPart_000_0185_01C3CF8B.993AB0B0--|||Tom and David,
Sincere thanks. Both methods seem to produce the same result - exactly what
I need. If you only knew the trouble I was having. Gives me something to
read up on.
Much appreciated,
Mike
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:UtmdnWcR0ITBcm-i4p2dnA@.giganews.com...
> SELECT [name],
> COUNT(CASE activity WHEN 'victim' THEN 1 END) AS victim_count,
> COUNT(CASE activity WHEN 'arrest' THEN 1 END) AS arrest_count,
> COUNT(CASE activity WHEN 'suspect' THEN 1 END) AS suspect_count
> FROM Sometable
> GROUP BY [name]
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>|||Greetings...
Is there a way to do this type of Query from the View designer or must I do
it from query analyzer'
Thanks!
S.
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:Ofw7lO7zDHA.2456@.TK2MSFTNGP12.phx.gbl...
> I've been trying to do this on and off for over a month, just can't seem
to
> get it. I have a table that looks something like
> Name Activity
> ==== ========> Mike Arrest
> John Victim
> Fred Suspect
> John Suspect
> Mike Victim
> John Victim
> Mike Arrest
> For graphing purposes, I'd like to end up with a view that looks like
> Name Victim_Count Arrest_Count Suspect_Count
> ==== ============ ============ =============> Mike 1 2 0
> John 2 0 1
> Fred 0 0 1
> I've tried things like select name, (select count(*) where activity => 'Victim') as Victim_Count group by name. Which doesn't work.
> Can anyone point me in the right direction? I know there are fixed number
of
> Activity types (3 in this case).
> Any help is much appreciated,
> Mike
>
No comments:
Post a Comment