CODEWITHSUNDEEP
mysqlsql
John
John

Reputation: 123

Query counts using inner selects

I am having an issue with using inner selects where the output isn't quite right. Any help would be greatly appreciated.

Here is my SQLFiddle example.

Here is the query I am using.

SELECT 
t.event as event_date,
count((
    SELECT
        count(s.id)
    FROM mytable s
    WHERE s.type = 2 AND s.event = event_date
)) AS type_count,
count((
    SELECT
        count(s.id)
    FROM mytable s
    WHERE s.type != 3 AND s.event = event_date
)) as non_type_count
FROM mytable t
WHERE t.event >= '2013-10-01' AND t.event <= '2013-10-08'
GROUP BY t.event

My current output:

October, 01 2013 00:00:00+0000 / 2 / 2

October, 03 2013 00:00:00+0000 / 1 / 1

The output I am trying to get:

October, 01 2013 00:00:00+0000 / 1 / 2

October, 03 2013 00:00:00+0000 / 0 / 0

So if you look at my query I am trying to use, I am basically trying to query the table in date range, then using inner selects get the rows that match the type. Thanks for your help in advance.

Upvotes: 2

Views: 60

Answers (3)

krokodilko
krokodilko

Reputation: 36117

Try this way:

SELECT 
    t.event as event_date,
    SUM( case when type = 2 then 1 else 0 end )
       AS type_count,
    SUM( case when type != 3 then 1 else 0 end )
       as non_type_count
FROM mytable t
WHERE t.event >= '2013-10-01' AND t.event <= '2013-10-08'
GROUP BY t.event

demo: --> http://sqlfiddle.com/#!2/19f3d/13

Upvotes: 1

Hart CO
Hart CO

Reputation: 34784

Can simplify a bit and exclude the sub-selects using conditional aggregation:

SELECT 
    t.event as event_date,
    SUM(t.type = 2) AS type_count,
    SUM(t.type != 3)AS non_type_count
FROM mytable t
WHERE t.event >= '2013-10-01' AND t.event <= '2013-10-08'
GROUP BY t.event

Demo: SQL Fiddle

This works in MySQL because an expression returns 1 or 0 for true/false. In other databases you can achieve the same via SUM(CASE WHEN type=2 THEN 1 END)

Upvotes: 3

Joe
Joe

Reputation: 6827

You're counting a count (count(select count(...). Try this:

SELECT 
    t.event as event_date,
    (SELECT
            count(s.id)
        FROM mytable s
        WHERE s.type = 2 AND s.event = event_date
    ) AS type_count,
    (SELECT
            count(s.id)
        FROM mytable s
        WHERE s.type != 3 AND s.event = event_date
    ) as non_type_count
FROM mytable t
WHERE t.event >= '2013-10-01' AND t.event <= '2013-10-08'
GROUP BY t.event

Upvotes: 0

Related Questions