CODEWITHSUNDEEP
sqloracle11g
user2924127
user2924127

Reputation: 6242

Get counts based on a group of dates

I have two tables:

Table 1 which the fields 

o_id (Fk),
dt

I have Table 2

o_id (Fk),
attr1 (Number)

My objective is count how many occurrences of attr1 occur where the value is from 0-10, 11-20, 21+. I then need to take these occurrences and Group them by dt from Table 1

To make it more clear I provide a sample output:

|   Date   |count(0-10) | count(11-20) | count(21+) |
-----------------------------------------------------
|  01/13   |       3    |         5    |     13     |
|  02/13   |       2    |         7    |     10     |

This is the closest I can get but it doesn't quite work:

SELECT
    t1.dt,
   (select count(tt2.o_id) from table1 tt1, table2 tt2 WHERE attr1 BETWEEN 0 AND 10),
   (select count(tt2.o_id) from table1 tt1, table2 tt2 WHERE attr1 BETWEEN 11 AND 20),
   (select count(tt2.o_id) from table1 tt1, table2 tt2 WHERE attr1 > 21),
FROM
   table1 t1, table2 t2
WHERE
   t1.o_id = t2.o_id
GROUP BY
   t1.dt;

The results I get now:

|   Date   |count(0-10) | count(11-20) | count(21+) |
-----------------------------------------------------
|  01/13   |       3    |         5    |     13     |
|  02/13   |       3    |         5    |     13     |
|  02/14   |       3    |         5    |     13     |
|  02/15   |       3    |         5    |     13     |
|  02/16   |       3    |         5    |     13     |
|  02/17   |       3    |         5    |     13     |
|  02/18   |       3    |         5    |     13     |

Just keeps repeating inside the counts column.

Upvotes: 0

Views: 50

Answers (4)

user4655509
user4655509

Reputation: 81

SELECT
t1.dt
,(select count(tt2.o_id) from table1 tt1, table2 tt2 WHERE attr1 BETWEEN  0 AND 10 AND t1.o_id=t2,o_id)
,(select count(tt2.o_id) from table1 tt1, table2 tt2 WHERE attr1 BETWEEN 11 AND 20 AND t1.o_id=t2,o_id)
,(select count(tt2.o_id) from table1 tt1, table2 tt2 WHERE attr1 > 21 AND t1.o_id=t2,o_id)
FROM table1 t1, table2 t2 
WHERE t1.o_id = t2.o_id 
GROUP BY t1.dt;

Upvotes: 1

jpw
jpw

Reputation: 44871

The issue is that you don't join the tables in the subqueries, but in any case this query can be done as a conditional aggregation which at least I would think look a bit better:

SELECT
    t1.dt,
    SUM(CASE WHEN attr1 BETWEEN 0 AND 10 THEN 1 ELSE 0 END) AS "count(0-10)",
    SUM(CASE WHEN attr1 BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS "count(11-20)",
    SUM(CASE WHEN attr1 > 21 THEN 1 ELSE 0 END) AS "count(21+)"
FROM
   table1 t1
JOIN
   table2 t2
ON
   t1.o_id = t2.o_id
GROUP BY
   t1.dt;

Upvotes: 2

dnoeth
dnoeth

Reputation: 60462

This can be done using SUM(CASE):

SELECT
    t1.dt,
    count(case when attr1 BETWEEN  0 AND 10 then tt2.o_id end),
    count(case when attr1 BETWEEN 11 AND 20 thent t2.o_id end),
    count(case when attr1 > 21              thent t2.o_id end)
FROM
   table1 t1, table2 t2
WHERE
   t1.o_id = t2.o_id
GROUP BY
   t1.dt;

Upvotes: 1

John Bollinger
John Bollinger

Reputation: 180103

You don't need subqueries. This will do what you seem to want:

SELECT
    t1.dt,
    count(CASE WHEN attr1 BETWEEN  0 AND 10 THEN 1 END),
    count(CASE WHEN attr1 BETWEEN 11 AND 20 THEN 1 END),
    count(CASE WHEN attr1 > 20 THEN 1 END),
FROM
   table1 t1
   JOIN table2 t2
     ON t1.o_id = t2.o_id
GROUP BY
   t1.dt
;

The COUNT() aggregate function counts the number of rows in each group for which the argument expression is not NULL. The CASE expressions that are being counted are non-null only when their (one each) WHEN condition is satisfied.

Upvotes: 2

Related Questions