SQL & PL/SQL

How about this?

SELECT COUNT(*)
  FROM (SELECT DISTINCT A, B, C
          FROM [TABLE_NAME])
          

with little info i guess something like:

select a, b, c, count(*)
  from <table>
 group by a, b, c

if you can post a sample data and output that will help us provide you an exact query.

select count(count(*)) from t group by a, b, c;

Thanks guys!
All these queries worked just fine!

I found one more:

select count(distinct a||b||c)
from table;

It's wrong. Different data can give the same concatenation result. Even if you'll add separators.

You are right Elic!
This will not work in all the cases.
It worked in my case because of the test values present in the table.

On a sidenote - i geuss i am too sleepy today. I should have come up with those queries and should not have posted my query without thinking of all the possible scenarios!

I need caffeine!

count(count(*)) ??? LOL

Your laughter is a sign of lack of knowledge. The last is unbelievable.

Look, Elic, for all practical purposes count(*) is equivalent to sum(1). Therefore:
1. Argument to the count doesn't make any sense
2. count aggregate function is redundant
Browse any mathematical book. Do you see any count symbols in it? Nope. Only sigmas (summation) and occasional pi's (multiplication). OK, even more infrequently lattice joins (maximum) and meets (minimum).

Фарисействуешь? Не вижу ни малейшей связи между этими псевдоумными словами и реакцией на моё сообщение. А смех без причины - признак дурачины. Жаль.

P.S. You can like sigmas or anything else. It's your right but indeed worthy of laughing.

I was perplexed by count(count(*)). One certainly can nest aggregation like sum(sum), or sum(max), or even sum(count), which is again essentially sum(sum), but why would you nest count? Or was it a joke?

It can't be a joke because it's trivial example. It does counting of group. It seems you don't want understand it, is it?

Count was, is and will be. Count was used, is used and will be used. Point. Counting clearly indicate perfomed operation. No matter how it is done internally.
Don't waste your and more my time. Leave your demagogy to dummies.

Inner count does nothing, except being syntactic sugar to indicate double level of aggregation.

select
-1+dense_rank(null,null,null) within group(order by a,b,c)
from table;

example

SQL> select max(Col1) as maxCol1,
  2  max(Col2) as maxCol2,
  3  -1+dense_rank(null) within group(order by Col1) as DistinctCol1,
  4  -1+dense_rank(null) within group(order by Col2) as DistinctCol2,
  5  -1+dense_rank(null,null) within group(order by Col1,Col2) as DistinctCol1Col2
  6  from (select 1 as Col1,1 as Col2 from dual
  7  union all select 1,1 from dual
  8  union all select 1,1 from dual
  9  union all select 1,1 from dual
 10  union all select 1,1 from dual
 11  union all select 1,1 from dual
 12  union all select 1,2 from dual
 13  union all select 1,3 from dual
 14  union all select 3,1 from dual
 15  union all select 3,2 from dual
 16  union all select 3,8 from dual
 17  union all select 4,1 from dual
 18  union all select 4,1 from dual
 19  union all select 4,1 from dual
 20  union all select 4,1 from dual
 21  union all select 6,1 from dual
 22  union all select 7,1 from dual);

  MAXCOL1    MAXCOL2  DISTINCTCOL1  DISTINCTCOL2  DISTINCTCOL1COL2
---------  ---------  ------------  ------------  ----------------
        7          8             5             4                 9

example2 (ReverseSort)

SQL> with DistinctTest as(
  2  select 1 as val1,1 as val2 from dual
  3  union all select 2,2 from dual
  4  union all select 2,2 from dual
  5  union all select 3,3 from dual
  6  union all select 3,3 from dual
  7  union all select 7,4 from dual)
  8  select val1,val2,
  9  dense_rank() over(order by val1 asc ,val2 asc)+
 10  dense_rank() over(order by val1 desc,val2 desc)-1 as distinctCount
 11    from DistinctTest
 12  order by val1,val2;

     VAL1       VAL2  DISTINCTCOUNT
---------  ---------  -------------
        1          1              4
        2          2              4
        2          2              4
        3          3              4
        3          3              4
        7          4              4

And it is what we need to "count distinct combinations" with a simple query. Someone can use any resulting not null aggregate function as inner. But count is simplest and most obvious one.

You had just not understood syntax from the first. Is it sufficient reason to loudly claim a bug? I don't think so :-)

well, to me, the inner count definitely does something

SQL> ed
Wrote file afiedt.buf

  1  with t as (
  2    select 1 a, 2 b, 3 c from dual union all
  3    select 1, 2, 3 from dual union all
  4    select 4, 5, 6 from dual union all
  5    select 7, 8, 9 from dual
  6  )
  7  select count(count(*))
  8  from t
  9* group by a, b, c
SQL> /

COUNT(COUNT(*))
---------------
              3

SQL> ed
Wrote file afiedt.buf

  1  with t as (
  2    select 1 a, 2 b, 3 c from dual union all
  3    select 1, 2, 3 from dual union all
  4    select 4, 5, 6 from dual union all
  5    select 7, 8, 9 from dual
  6  )
  7  select count(*)
  8  from t
  9* group by a, b, c
SQL> /

  COUNT(*)
----------
         1
         2
         1

Suppose i want a distinct on 2 columns in a subquery.

like this one,

SELECT DISTINCT PR.proc_model_id, PM.proc_model_desc,
(
SELECT COUNT (DISTINCT C.projid ) FROM PMO_PROJECTS AS P INNER JOIN PMO_PROJ_CON AS C
ON C.projID = P.prj_id
) AS prj_count
FROM
PMO_PROC_MODELS AS PM
INNER JOIN PMO_PROJ AS PR
ON PR.proc_model_id = PM.proc_model_id
ORDER BY
PM.proc_model_desc ASC


Thanks.

Any Other Option for such a requirement?

I still don't understand it. Think of it, what is the count? What do we count? Simple: we count elements of a set. Now when you insist that there is an argument to the count, it must be a set that you plug into the count, right? So, when you write count(count(DEPTNO)) is the inner count(DEPTNO) some kind of set? Clearly not. Functions in SQL accept column values as arguments, not sets!

Next, what the simple aggregate count(*) can possibly mean? The "*" is the set of all columns. Therefore, we must be counting all the columns when we write count(*), right?

In a word, this whole count business in SQL is ridiculous. You just got used to it so much that don't pay attention to glaring inconsistencies.

<Фарисействуешь? Не вижу ни малейшей связи между этими
псевдоумными словами и реакцией на моё сообщение. А смех без причины - признак
дурачины. Жаль.>

Wow, you can speak Russian? Very useful in an English technical forum.
Thanks to worldlingo free translation service (I thought this might convey
something meaningful), sure something was lost in its
translation:

You ________? I see not the least connection between these pseudo-clever words 
and reaction for my communication. But laughter without the reason - sign of 
_______. It is a pity.

Even though the message in question in more appropriate to private communication rather than public forum, it is not as bad as it sounds. Especially that Elic is from my native city.

This whole thread may look worthless, but this is how I found this gem:

select sum(sum(sal)*0+1) from emp
group by deptno