From: Linn Kubler on
Hi,

I am stuck with a programming problem and I'm wondering if anyone can help
me. Running SQL 2005.

Here's a sample of my data:

CREATE TABLE #temp (class varchar(1), referral varchar(2))



INSERT INTO #temp VALUES ('A', 'BB')

INSERT INTO #temp VALUES ('A', 'AA')

INSERT INTO #temp VALUES ('B', 'BB')

INSERT INTO #temp VALUES ('B', 'CC')

INSERT INTO #temp VALUES ('C', 'AA')

INSERT INTO #temp VALUES ('C', 'BB')

INSERT INTO #temp VALUES ('A', 'DD')

INSERT INTO #temp VALUES ('C', 'DD')



What I want is a result that looks like this:



Class In Out

A 2 1

B 1 1

C 2 1



Basically I want to count the number of classes and separate them by the
referral criteria. In this example I chose BB and DD for the In group and
AA and CC in the Out group.



I thought I could do this using the Case When construct like this:

SELECT #temp.class,

(CASE WHEN #temp.referral IN ('BB', 'DD') THEN COUNT(#temp.class) ELSE
0 END) AS 'In',

(CASE WHEN #temp.referral NOT IN ('BB', 'DD') THEN COUNT(#temp.class)
ELSE 0 END) AS 'Out'

FROM #temp

GROUP BY #temp.class, #temp.referral



But that doesn't work correctly. So I'm stumped, isn't there a way to
accomplish this?



I tried looking at Pivot but can't get it to work either:

SELECT class, referral FROM #temp

Pivot

(

COUNT(class) FOR referral IN ('AA','BB','CC', 'DD')

) AS pvt

ORDER BY class



But this just gives me a syntax error near (.



And I don't really think pivot is an option since my database needs to run
in SQL 2000 compatibility mode.



Any help is greatly appreciated.



Thanks in advance,

Linn




From: Iain Sharp on
On Tue, 8 Jun 2010 10:48:33 -0500, "Linn Kubler"
<lkubler(a)chartwellwisc2.com> wrote:

>Hi,
>
>I am stuck with a programming problem and I'm wondering if anyone can help
>me. Running SQL 2005.
>
>Here's a sample of my data:
>
>CREATE TABLE #temp (class varchar(1), referral varchar(2))
>
>
>
>INSERT INTO #temp VALUES ('A', 'BB')
>
>INSERT INTO #temp VALUES ('A', 'AA')
>
>INSERT INTO #temp VALUES ('B', 'BB')
>
>INSERT INTO #temp VALUES ('B', 'CC')
>
>INSERT INTO #temp VALUES ('C', 'AA')
>
>INSERT INTO #temp VALUES ('C', 'BB')
>
>INSERT INTO #temp VALUES ('A', 'DD')
>
>INSERT INTO #temp VALUES ('C', 'DD')
>
>
>
>What I want is a result that looks like this:
>
>
>
>Class In Out
>
>A 2 1
>
>B 1 1
>
>C 2 1
>
>
>
>Basically I want to count the number of classes and separate them by the
>referral criteria. In this example I chose BB and DD for the In group and
>AA and CC in the Out group.
>
SELECT #temp.class
,sum(CASE WHEN #temp.referral IN ('BB', 'DD') THEN 1 ELSE 0
END) AS 'In'
,sum(CASE WHEN #temp.referral NOT IN ('BB', 'DD') THEN 1 ELSE 0
END) AS 'Out'

FROM #temp

GROUP BY #temp.class


Sum 1 is equivalent to count....

Iain
From: Linn Kubler on
Ah yes, that's it! Thanks so much Iain and thanks for the quick reply.
Funny how the solutions seem obvious once you see them.

Thanks,
Linn

"Iain Sharp" <iains(a)pciltd.co.uk> wrote in message
news:g5qs06lpckmel2nq2cr60jec2jlegk4js4(a)4ax.com...
> On Tue, 8 Jun 2010 10:48:33 -0500, "Linn Kubler"
> <lkubler(a)chartwellwisc2.com> wrote:
>
>>Hi,
>>
>>I am stuck with a programming problem and I'm wondering if anyone can help
>>me. Running SQL 2005.
>>
>>Here's a sample of my data:
>>
>>CREATE TABLE #temp (class varchar(1), referral varchar(2))
>>
>>
>>
>>INSERT INTO #temp VALUES ('A', 'BB')
>>
>>INSERT INTO #temp VALUES ('A', 'AA')
>>
>>INSERT INTO #temp VALUES ('B', 'BB')
>>
>>INSERT INTO #temp VALUES ('B', 'CC')
>>
>>INSERT INTO #temp VALUES ('C', 'AA')
>>
>>INSERT INTO #temp VALUES ('C', 'BB')
>>
>>INSERT INTO #temp VALUES ('A', 'DD')
>>
>>INSERT INTO #temp VALUES ('C', 'DD')
>>
>>
>>
>>What I want is a result that looks like this:
>>
>>
>>
>>Class In Out
>>
>>A 2 1
>>
>>B 1 1
>>
>>C 2 1
>>
>>
>>
>>Basically I want to count the number of classes and separate them by the
>>referral criteria. In this example I chose BB and DD for the In group and
>>AA and CC in the Out group.
>>
> SELECT #temp.class
> ,sum(CASE WHEN #temp.referral IN ('BB', 'DD') THEN 1 ELSE 0
> END) AS 'In'
> ,sum(CASE WHEN #temp.referral NOT IN ('BB', 'DD') THEN 1 ELSE 0
> END) AS 'Out'
>
> FROM #temp
>
> GROUP BY #temp.class
>
>
> Sum 1 is equivalent to count....
>
> Iain


From: Plamen Ratchev on
You can use COUNT as well:

SELECT #temp.class,
COUNT(CASE WHEN #temp.referral IN ('BB', 'DD') THEN 1 END) AS
'In',
COUNT(CASE WHEN #temp.referral NOT IN ('BB', 'DD') THEN 1 END)
AS 'Out'
FROM #temp
GROUP BY #temp.class;

--
Plamen Ratchev
http://www.SQLStudio.com