[Resolved] 2 Complex SQL Select Statements Question

lanjoky

Hey all,

I need some help with 2 select statements

First I need a select statement that:
- is based on 2 int fields (Say field1 and field2)
- It needs to show all the (unique) combinations of those fields that are used in the data.

ie. combinations of field1/field2

Second I need a select statement that:
-adds to the above by counting the number of records in the database that use each combination.

its an oracle database using SQL Commands or PSQL scripts

any help you all can provide would be greatly appreciated!!

THANKS

Rodders

It's a while since I used Oracle, but I believe you can do this for your first question:


select distinct Field1, Field2
from TableA

However, if you want to count them then you could use a GROUP BY together with a concatenatation:


select CONCAT(Field1, ":", Field2), COUNT(*)
from TableA
GROUP BY CONCAT(Field1, ":", Field2)

You'll need to check exactly how to concatenate in Oracle. It may be a function like CONCAT or it may just be a case of saying Field1+Field2.........I can't remember and don't know where to look.

Hope this helps

Lars_Berg

Why concatenate?

select Field1, Field2, COUNT(*)
from TableA
GROUP BY Field1, Field2

Rodders

I haven't used this code (GROUP BY) for years and couldn't remember how it worked things out. I think I thought it would do some weird groupings based on the individual fields first, but now I think about it that would be impossible and useless.