Embedded SELECT/COUNT statement?

[ladygray]

Hello all,

I am trying to create a table output in Oracle so that I can analyze it using R with the RODBC package. I realize that I could just query the Oracle database from R using this package but as I am just learning both languages simultaneously (S and SQL) I think it would be better if I could export my table (the results of my query) from Oracle and then import it into R separately.

Okay, so here's the problem. I have two columns, sample year and species type. I would like Oracle to return to me a table that has the sample years in one column the species type in the next column (either softwood or hardwood) and the count of each in the next column. So it would look something like this:

SAMPYEAR SPTYPE COUNT
1965 S 4564
1965 H 1256
1966 S 7894
1966 H 4516 ... etc

I have tried the following two commands and gotten close:
select sampyear, sptype from density
group by sampyear, sptype
order by sampyear;

select count(*) from density where sptype='S' OR sptype='H'
group by sampyear,sptype
order by sampyear;


There must be a way to combine these two statements to get what I'm looking for. The first statement gives me the first two columns of what I'd like and the last statement gives me the third column. I have tried executing a SELECT COUNT(*)(colnames,...) WHERE statement but with no luck. Any help or resources of where I could look for this answer would be great.

Thanks in advance,
Ladygray

PS: in case it matters, I am using OracleXE 10g express edition, the SQL Developer.

[Gray Cells]

Hi,

I think you were close to the answer yourself. Just combine the two queries and you will get:

select sampyear
, sptype
, count(sptype)
from density
where sptype='S'
OR sptype='H'
group by sampyear
, sptype
order by sampyear;

You can also use the IN-condition.

select sampyear
, sptype
, count(sptype)
from density
where sptype in ( 'S', 'H' )
group by sampyear
, sptype
order by sampyear;

Test it and tell if this solves your problem.

GC

[ld_be]

Hello


I think this is also possible.

select sampyear, sptype, count(*)
from density
group by sampyear, sptype
order by sampyear;


Greetings
ld_be

[Gray Cells]

Hi,

Yes, that will work fine.

There are only two slight differences.

The first is that when you do not use the '*' it will count only the not null values.

The second is that when you do not use the '*' it will be a little bit faster, because not every column will be counted.

Gray Cells

[ladygray]

Thank you so much for your help! I really appreciate it!