CODEWITHSUNDEEP
sqloracle-databasecountgroup-by
byle.05
byle.05

Reputation: 196

Building query: Distinct count

I need help on generating a Oracle SQL query...

The thing is that I have the following table:

COL1  COL2  COL3
----------------
1     A     XXXX
1     B     LLLL
1     F     TTTT
5     B     GGGG
5     C     VVVV
7     D     FFFF
7     E     AAAA
7     S     SSSS

The thing is that I need a query to show all the columns plus an extra column to show the count of the different COL1 values.

In the example we have 3 different values for COL1 (1, 5 and 7)

So the query should show 3 in the extra column. Something like:

SELECT COL1, COL2, COL3, COUNT(DISTINCT COL1) DIFF_COUNT FROM TABLE1....


COL1  COL2  COL3  DIFF_COUNT
----------------------------
1     A     XXXX  3
1     B     LLLL  3
1     F     TTTT  3
5     B     GGGG  3
5     C     VVVV  3
7     D     FFFF  3
7     E     AAAA  3
7     S     SSSS  3

I have tried many ways and I know that maybe its as simple as a COUNT with the corresponding group by but i havent been able to achieve it due to the need of having to keep the COL2 and COL3 in the results.

Any help on it?

Thanks a lot in advance!!

Upvotes: 1

Views: 666

Answers (5)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

You could use COUNT() OVER() analytic function along with DISTINCT.

For example,

SQL> SELECT t1.*,
  2         COUNT(distinct col1) OVER() DIFF_COUNT
  3  FROM t1;

      COL1 C COL3 DIFF_COUNT
---------- - ---- ----------
         1 A XXXX          3
         1 B LLLL          3
         1 F TTTT          3
         5 B GGGG          3
         5 C VVVV          3
         7 D FFFF          3
         7 E AAAA          3
         7 S SSSS          3

8 rows selected.

SQL>

Alternatively, without using analytic function, you could do it using a subquery as an INLINE VIEW:

SQL> SELECT t1.*,
  2    (SELECT COUNT (*) FROM
  3      (SELECT DISTINCT col1 FROM t1
  4      )
  5    ) AS DIFF_COUNT
  6  FROM t1;

      COL1 C COL3 DIFF_COUNT
---------- - ---- ----------
         1 A XXXX          3
         1 B LLLL          3
         1 F TTTT          3
         5 B GGGG          3
         5 C VVVV          3
         7 D FFFF          3
         7 E AAAA          3
         7 S SSSS          3

8 rows selected.

SQL>

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

The simplest way is:

select col1, col2, col3, count(distinct col1) over() cnt from test

SQLFiddle demo

Upvotes: 2

Jeremy C.
Jeremy C.

Reputation: 2465

This is probably a bad solution but seeing as you haven't gotten an answer yet and I was messing around and got something that pretty much is what you wanted I'll put it here anyways:

SELECT t1.*, 
  (SELECT count (*)
          FROM (SELECT DISTINCT col1
                FROM t1)
  ) AS DIFF_COUNT
FROM t1

There are probably better solutions but this is the only one I could figure out SQLFIDDLE if you want to check it out: http://sqlfiddle.com/#!4/32578/36

Upvotes: 0

Samiul Al Hossaini
Samiul Al Hossaini

Reputation: 1050

SELECT 
col1, 
col2, 
col3,       
count(col1) OVER(PARTITION BY col1 ORDER BY col1) DIFF_COUNT
FROM table_tmp;



     COL1  COL2     COL3     DIFF_COUNT
---------- -----   ----     ----------
         1 A       XXXX          3
         1 B       LLLL          3
         1 F       TTTT          3
         5 B       GGGG          2
         5 C       VVVV          2
         7 D       FFFF          3
         7 E       AAAA          3
         7 S       SSSS          3

Upvotes: 0

Razieltje
Razieltje

Reputation: 357

Depending on what the rest of the query should do, you could work with a subquery?

SELECT COL1, COL2, COL3, (SELECT DISTINCT count(COL1) FROM TABLE1) AS DIFF_COUNT FROM TABLE1....

you could even use the subquery to make different results per line that way

Upvotes: 0

Related Questions