Query question - Separating Columns?
in [Programming SQL]
|
Prev: Cast Varchar to Decimal?
Next: Does asynchronous operation supported by oledb provider for openquery?
From: Linn Kubler on 8 Jun 2010 11:48 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 8 Jun 2010 11:59 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 8 Jun 2010 12:37 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 8 Jun 2010 17:34
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 |