CODEWITHSUNDEEP
sqlselectinformix
insict
insict

Reputation: 881

JOIN the same table

Hi I have table badge which has columns facility and date (other columns are not important). I want to select count(*) which should look like this:

DATE       | FACILITY 4 COUNT | FACILITY 1 COUNT
12/12/2012 | 234              |  647

I wrote two separate queries for facility 1 and 4 but how to join it to one Query;

SELECT
    date,
    COUNT(*)
    FROM      badge
    WHERE     facility = 3
    AND       xtype = 19
    AND       date BETWEEN 'some_date' AND 'some_date'
    GROUP BY  date

another query differs only in the value of facility.

EDIT: It's informix database.

Upvotes: 1

Views: 89

Answers (2)

RobJohnson
RobJohnson

Reputation: 885

SELECT date,
       SUM(case when facility = 1 then 1 else 0 end) "FACILITY 1 COUNT",
       SUM(case when facility = 4 then 1 else 0 end) "FACILITY 4 COUNT"
FROM      badge
WHERE     (facility = 1 OR facility = 4)
AND       xtype = 19
AND       date BETWEEN 'some_date' AND 'some_date'
GROUP BY  date

Upvotes: 3

John Woo
John Woo

Reputation: 263723

give this a try,

SELECT  date,
        SUM(CASE WHEN facility = 3 THEN 1 ELSE 0 END) `Facility 3 Count`,
        SUM(CASE WHEN facility = 1 THEN 1 ELSE 0 END) `Facility 1 Count`
FROM      badge
WHERE     xtype = 19 AND      
          date BETWEEN 'some_date' AND 'some_date'
GROUP     BY  date

Upvotes: 2

Related Questions