filtering data from one column into two columns and performing an outer join

Daniel993
Daniel993 used Ask the Experts™
on
Hello Experts

I’m facing a bit of a problem, I need to count the occurrences of ITEM1 and ITEM2 in the same column and put them into separate columns in order to perform calculations on them...below I have three queries and I don't know how to put them into one statement but I thought that if I do a left outer join on the tables (T2 and T3 added to T1 on Date) then that would get me the result:

SELECT T1.DATE from TABLE T1
SELECT T2.DATE,count (Variable1) from TABLE T2 as ITEM1 where Variable1 like ’ITEM1’ group by Date
SELECT T3.DATE,count (Variable1) from TABLE T3 as ITEM2 where Variable1 like ’ITEM2’ group by Date

Open in new window


OTOH searching the net I found that some sites suggested that this problem could be addressed with CTE’s (but it seems a lot more complicated than it should be, or is it just me.)

WITH DATE (Date) AS
(
   SELECT Date FROM TABLE T1
),

ITEM1 (Date,Variable1) AS
(
   SELECT
      Date,
      Variable1
      (SELECT Date, COUNT(Variable1) FROM TABLE T2
       WHERE Variable1 like ’ITEM1’ group by Date) as ITEM1
   FROM TABLE T2
),

ITEM2 (Date,Variable1) AS
(
   SELECT
      Date,
      Variable1
      (SELECT Date, COUNT(Variable1) FROM TABLE T3
       WHERE Variable1 like ’ITEM2’ group by Date) as ITEM2
   FROM TABLE T3
),


SELECT T1.DATE, T2.Date,T2.Variable1,T3.Date, T3.Variable1

FROM TABLE T1
   LEFT OUTER JOIN TABLE T2 ON
      TABLE T1.Date = TABLE T2. Date
   LEFT OUTER JOIN TABLE T3 ON
TABLE T1.Date = TABLE T3. Date

Open in new window


Hence what would be the best approach to address such a problem ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
Since you used no wild cards, you may as well use the = operator and not like:

SELECT [DATE],
    SUM(CASE WHEN Variable1  = 'ITEM1' THEN 1 ELSE 0 END) AS ITEM1,
    SUM(CASE WHEN Variable1  = 'ITEM2' THEN 1 ELSE 0 END) AS ITEM2
FROM [TABLE]
GROUP BY [DATE]

Open in new window

Commented:
Hi,

Could you give the table structure and some sample data and the expected output?
Thanks.

Author

Commented:
Tried it this morning and it works like a charm, thanks...
As for my second script, talk about an overkill :)

Agusacil, already got the solution, thanks for the offer.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial