Home » SQL & PL/SQL » SQL & PL/SQL » Count data (10.2)
Count data [message #577907] Thu, 21 February 2013 12:53 Go to next message
lott42
Messages: 96
Registered: June 2010
Member
I think this is easier than I think but all I want to do is count the distinct rows in a table.

WITH data AS
 (SELECT 1 id1, 1 id2, 111 val1
    FROM dual
  UNION ALL
  SELECT 1, 2, 222
    FROM dual
  UNION ALL
  SELECT 2, 1, 111
    FROM dual
  UNION ALL
  SELECT 2, 2, 222
    FROM dual
  UNION ALL
  SELECT 2, 3, 222 FROM dual)
SELECT t.*, COUNT(*) over() cnt1
  FROM (SELECT DISTINCT id1, val1 FROM data) t
 ORDER BY id1, val1


seems to get what I want

   	ID1	VAL1	CNT1
1	1	111	4
2	1	222	4
3	2	111	4
4	2	222	4


but can the count be done in a stand alone query such as??
SELECT DISTINCT id1,
                val1,
                COUNT(*) over() cnt1,
                COUNT(*) over(PARTITION BY id1) cnt2
  FROM data
 ORDER BY id1, val1


NOT what I want

   	ID1	VAL1	CNT1	CNT2
1	1	111	5	2
2	1	222	5	2
3	2	111	5	3
4	2	222	5	3



Re: Count data [message #577910 is a reply to message #577907] Thu, 21 February 2013 13:11 Go to previous messageGo to next message
Bill B
Messages: 1102
Registered: December 2004
Senior Member
select id1,val1,count(*) cnt
from data
group by id1,val1
order by id1,val1;
Re: Count data [message #577911 is a reply to message #577910] Thu, 21 February 2013 13:18 Go to previous messageGo to next message
lott42
Messages: 96
Registered: June 2010
Member
Doesn't

select id1,val1,count(*) cnt
from data
group by id1,val1
order by id1,val1;


Return

  	ID1	VAL1	CNT
1	1	111	1
2	1	222	1
3	2	111	1
4	2	222	2


??

I would like the total "Cnt" of distinct rows:

   	ID1	VAL1	CNT1
1	1	111	4
2	1	222	4
3	2	111	4
4	2	222	4




Re: Count data [message #577913 is a reply to message #577911] Thu, 21 February 2013 13:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where does 4 come from?
As far as I can see all rows are distinct:
SQL> WITH data AS
  2   (SELECT 1 id1, 1 id2, 111 val1
  3      FROM dual
  4    UNION ALL
  5    SELECT 1, 2, 222
  6      FROM dual
  7    UNION ALL
  8    SELECT 2, 1, 111
  9      FROM dual
 10    UNION ALL
 11    SELECT 2, 2, 222
 12      FROM dual
 13    UNION ALL
 14    SELECT 2, 3, 222 FROM dual)
 15  select * from data;
       ID1        ID2       VAL1
---------- ---------- ----------
         1          1        111
         1          2        222
         2          1        111
         2          2        222
         2          3        222

5 rows selected.


Regards
Michel

[Updated on: Thu, 21 February 2013 13:29]

Report message to a moderator

Re: Count data [message #577915 is a reply to message #577913] Thu, 21 February 2013 14:45 Go to previous messageGo to next message
lott42
Messages: 96
Registered: June 2010
Member
The rows are not distinct if you only look at two columns(id1, val1).

SELECT id1, val1 FROM data
 ORDER BY id1, val1

   	ID1	VAL1
1	1	111
2	1	222
3	2	111
4	2	222
5	2	222



in my original query, I want the count(4 in this case) of the total number of rows returned that meet this condition

WITH data AS
 (SELECT 1 id1, 1 id2, 111 val1
    FROM dual
  UNION ALL
  SELECT 1, 2, 222
    FROM dual
  UNION ALL
  SELECT 2, 1, 111
    FROM dual
  UNION ALL
  SELECT 2, 2, 222
    FROM dual
  UNION ALL
  SELECT 2, 3, 222 FROM dual)
SELECT t.*, COUNT(*) over() cnt1
  FROM (SELECT DISTINCT id1, val1 FROM data) t
 ORDER BY id1, val1

   	ID1	VAL1	CNT1
1	1	111	4
2	1	222	4
3	2	111	4
4	2	222	4


Re: Count data [message #577917 is a reply to message #577915] Thu, 21 February 2013 15:19 Go to previous message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The rows are not distinct if you only look at two columns(id1, val1).


So give a correct specification in your topic.

SQL> WITH data AS
  2   (SELECT 1 id1, 1 id2, 111 val1
  3      FROM dual
  4    UNION ALL
  5    SELECT 1, 2, 222
  6      FROM dual
  7    UNION ALL
  8    SELECT 2, 1, 111
  9      FROM dual
 10    UNION ALL
 11    SELECT 2, 2, 222
 12      FROM dual
 13    UNION ALL
 14    SELECT 2, 3, 222 FROM dual),
 15    vals as (select distinct id1, val1 from data),
 16    cnt as (select count(*) cnt from vals)
 17  select t.*, c.cnt from data t, cnt c
 18  /
       ID1        ID2       VAL1        CNT
---------- ---------- ---------- ----------
         1          1        111          4
         1          2        222          4
         2          1        111          4
         2          2        222          4
         2          3        222          4

5 rows selected.


Regards
Michel
Previous Topic: Need equivalent DECODE for CASE statement
Next Topic: CASE STMT VS DECODE
Goto Forum:
  


Current Time: Tue Sep 23 22:27:19 CDT 2014

Total time taken to generate the page: 0.09246 seconds