-1

It seems that I haven't been clear enough. The query that seems to work is:

Select ((Select count (table1.id) from table1  where table1.code=2 and table1.name=5) as ‘name5’,
(Select count (table1.id) from table1  where table1.code=2 and table1.name=7) as ‘name7’)
From table.1;
union
Select ((Select count (table1.id) from table1  where table1.code=5 and table1.name=5) as ‘name5’,
(Select count (table1.id) from table1  where table1.code=5 and table1.name=7) as ‘name7’)

From table.1;
union
Select ((Select count (table1.id) from table1  where table1.code=15 and table1.name=5) as ‘name5’,
(Select count (table1.id) from table1  where table1.code=15 and table1.name=7) as ‘name7’)
From table.1;

….

Which gets an outcome like this:

name5  name7
    52         47
    42         84
    61         11

My problem is that the table1.code has a thousand and more values other than 2,5 and 15 and I can not repeat a union statement for so many times.

Crazyshezy
  • 1,530
  • 6
  • 27
  • 45
user7523991
  • 1
  • 1
  • The above given query is not a valid one. Also, please try explaining your problem again. Why do you want to repeat the sql with different value for the same purpose? You can just directly do a `code in (1, 2, 3, 4, 5 ... )` or make `code between 1 and 100` – Gurwinder Singh Feb 07 '17 at 16:36
  • How do you define which table1.codes you want? you could use key words `in`, or `exists` or perform a join on table1 to limit the results. – xQbert Feb 07 '17 at 16:48
  • Out of curiosity, are you trying to reproduce the [N+1 problem](http://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem) in PL/SQL? – Álvaro González Feb 07 '17 at 16:50

2 Answers2

1

Well it seems like you actually just want to group by the values in the code column, and you can use IN or EXISTS

select count(table1.id) as theCount, table1.code as theCode
 from table1 where table1.code in ('code a','code b', 'etc...')
group by table1.code;

the output would be

theCount||theCode
code a || 8074
code b || 34
etc... || 9575

or something like that but with non notional numbers for counts HTH

Mark Giaconia
  • 3,844
  • 5
  • 20
  • 42
  • The query that seems to work is: Select (Select count (table1.id) from table1 where table1.code=2 and table1.name=5) as ‘name5’, (Select count (table1.id) from table1 where table1.code=2 and table1.name=7) as ‘name7’) From table.1; union Select (Select count (table1.id) from table1 where table1.code=5 and table1.name=5) as ‘name5’, (Select count (table1.id) from table1 where table1.code=5 and table1.name=7) as ‘name7’) From table.1; – user7523991 Feb 08 '17 at 20:46
0

You could try to list all values in a nested select listing integers from a to your value, e.g. 100, like that:

select count table1.id from table1 one where table1.code in (
     select rownum from all_objects where rownum < 100
);

or if you don't want to start at "1":

select count table1.id from table1 one where table1.code in (
    select rownum n from dual connect by level 10 where n>3
);
J. Chomel
  • 8,193
  • 15
  • 41
  • 69