Home » SQL & PL/SQL » SQL & PL/SQL » Count data (10.2)
| Count data [message #577907] |
Thu, 21 February 2013 12:53  |
lott42
Messages: 54
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 #577911 is a reply to message #577910] |
Thu, 21 February 2013 13:18  |
lott42
Messages: 54
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 |
 |
Michel Cadot
Messages: 54129
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 |
lott42
Messages: 54
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 |
|
Michel Cadot
Messages: 54129
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
|
|
|
|
Goto Forum:
Current Time: Sun May 19 22:22:25 CDT 2013
Total time taken to generate the page: 0.24446 seconds
|
] 
Members
Search
Help
Register
Login
Home
