Need two column count from one field (one for value x, one for value y 1

FromAjo · Nov 11, 1999

I am doing a select count valuex as x, count valuex as y. In other words I need to count different values from the same field and have them print out two separate counts. I'm stumped. Please, if anyone has any answers....

carp · Nov 13, 1999

Please give a sample of your data and the desired output; it will make it easier to help you.

calahans · Nov 15, 1999

Hi, 
 
Like Carp, I'm not sure what you are trying to achieve. However, your SQL will look something like: 
 
SELECT COUNT(VAL1) AS V1, COUNT(VAL1) AS V2 
FROM TABLE 
WHERE ......; 
 
This will give you the same value twice. I can't see how you can get two differing values from the same column in the one SQL statement. The values are decided by your WHERE clause, this section restricts what you are selecting on. 
 
HTH, 
 
C

carp · Nov 15, 1999

OK, let me guess at what you're after: 
 
I think what you are trying to do is count how many occurrences of certain values occur in a column. For instance, if the data in your column looks like: 
 
X 
--------- 
3 
5 
5 
5 
3 
 
then you would like your output to look like: 
 
count_of_3 .............count_of_5 
..............2... ...................... 3 
(Ignore the periods - I just have to put something in to maintain the space between columns.) 
 
This can be done with the following query: 
 
SQL> SELECT(DECODE(x,3,1,NULL)) count_of_3, COUNT(DECODE(x,5,1,NULL)) count_of_5 
2 FROM your_table; 
 
Hopefully you aren't testing for too many values. 
 
To make this dynamic (so that you don't need to know the range of values ahead of time), you would probably be better off creating a procedure so you can pull the values into a cursor and explicitly count them. 
 
Let us know if this answers the question.

calahans · Nov 16, 1999

One other thing that strikes me, is that if you are doing this using SQL server you will probably have to write a stored procedure in TSQL to get your required results back. Unfortunately SQL Server has no ORACLE DECODE equivalent. The same goes for Ingres (I think). 
 
DECODE is not supported by the SQL ANSI 92 standard. Which is a pity, as carp showed how useful it is!

carp · Nov 16, 1999

Well, the good news is that there's more than one way to skin this cat - and at least one of them should be doable via ANSI standard SQL: 
 
Using the same sample data as before (and the same periods to maintain some spacing), you could try: 
 
SQL> SELECT count3.c3 count_of_3, count5.c5 count_of_5 
2 FROM (SELECT count(*) c3 FROM test WHERE x = 3) count3, 
3 (SELECT count(*) c5 FROM test WHERE x = 5) count5; 
 
COUNT_OF_3 ......COUNT_OF_5 
...........----------................ ---------- 
...................2..........................3 
 
I don't believe this conflicts w/ ANSI standards.

calahans · Nov 17, 1999

Carp, 
 
Thats a great solution. I didn't know you could do that (you live and learn!). I'll stick that syntax in memeory as I'm sure that it will come in handy. 
 
Thanks, 
 
C

amosgreg · Nov 19, 1999

Carps solution with the sub select will get the desired result however the overhead of multiple passes against the database/table/s could depending on the results desired be large. 
There is another solution that can get the result with 1 pass against the table/s and that is a 
 
select count(Distinct VAL1) from table1 
where VAL1 in(3,5) 
 
if you don't care whatr the result values are leave off the where clause. 
Greg

carp · Nov 19, 1999

Greg - you're absolutely right! Somehow I got the impression that FromAjo was looking for the results to be in two separate columns! Upon rereading the original request, this is a MUCH simpler problem. 
 
You can also get the different counts for an arbitrary number of different values and not have to know the specific values ahead of time with: 
 
SELECT x, count(x) 
FROM your_table 
GROUP BY x;

carp · Nov 19, 1999

Greg - On further examination, I don't think your query will return the desired results. Your query will return one number which represents how many different values are in the column. I believe what FromAjo wanted was the number of occurrences of different values. 
 
Of course, right now we're all guessing what FromAjo really wanted because he/she never responded to our earlier requests for clarification!

DarkMan · Nov 22, 1999

How about this? 
 
select x1=(select count(your_field) from your_table where your_field=1), 
x2=(select count(your_field) from your_table where your_field=2) 
from your_table Doug Trocino <a href=mailto:dtrocino@tecumsehgroup.com>dtrocino@tecumsehgroup.com</a> <a href=

http://www.tek-tips.com>Tek-Tips

Forums</a> Web Application Developer 
Tecumseh Group, Inc. 
Sponsors of Tek-Tips Forums

amosgreg · Nov 24, 1999

Carp, I was coding from memory without the avaliablity to run a simple test and you are correct!! 
My solution would only return the sum count of the 2 values. And as you stated so clearly we don't know what FromAjo was really looking for. 
Thanks Greg

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Need two column count from one field (one for value x, one for value y 1

FromAjo

Programmer

carp

MIS

calahans

Programmer

carp

MIS

calahans

Programmer

carp

MIS

calahans

Programmer

amosgreg

Programmer

carp

MIS

carp

MIS

DarkMan

Programmer

amosgreg

Programmer

Similar threads

Part and Inventory Search

Sponsor