CODEWITHSUNDEEP
sqloracle-databaseoracle11g
SomebodyOnEarth
SomebodyOnEarth

Reputation: 418

ORA-01427 - Need the counts of each value

I get "ORA-01427: single-row subquery returns more than one row" when I run the following query:

select count(*) 
from table1 
where to_char(timestamp,'yyyymmddhh24') = to_char(sysdate-1/24,'yyyymmddhh24') 
  and attribute = (select distinct attribute from table2);

I want to get the counts of each value of attribute in the specific time frame.

Upvotes: 1

Views: 119

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I would recommend writing this as:

select count(*)
from table1 t1
where timestamp >= trunc(sysdate-1/24, 'HH') and
      timestamp < trunc(sysdate, 'HH') and
      exists (select 1 from table2 t2 where t2.attribute = t1.attribute);

This formulation makes it easier to use indexes and statistics for optimizing the query. Also, select distinct is not appropriate with in (although I think Oracle will optimize away the distinct).

EDIT:

You appear to want to aggregate by attribute as well:

select t1.attribute, count(*)
from table1 t1
where timestamp >= trunc(sysdate-1/24, 'HH') and
      timestamp < trunc(sysdate, 'HH') and
      exists (select 1 from table2 t2 where t2.attribute = t1.attribute)
group by t1.attribute;

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726809

You can do it with a join and GROUP BY:

SELECT
    count(*) AS Cnt
,   a.attribute
FROM table1 t
JOIN table2 a ON t.attribute=a.attribute
WHERE to_char(t.timestamp,'yyyymmddhh24') = to_char(sysdate-1/24,'yyyymmddhh24')
GROUP BY a.attribute

This produces a row for each distinct attribute from table2, paired up with the corresponding count from table1.

Upvotes: 1

Related Questions