PL SQL : Query to count differents values of a field

Question

I have to do counts to the quantity of entitys in the database i have to obtain the quantity of registries for entities that appear

ENTITY OVERALL DATE HOUR
====== ===== ==== ====
ENT1 5 20100318 12:00
ENT2 20 20100318 12:00
ENT3 12 20100318 12:00

CURSOR1
SELECT distinct(rp.cod_entidad),
YYYYYYYYY,
to_date(to_char(SYSDATE,'YYYYMMDD'),'YYYY-MM-DD') as fecha_pago,
to_char(sysdate,'hh-mi-ss') as hora_pago
FROM registry rp, product pc
where pc.nro_solicitud = rp.nro_solicitud
and pc.resp_2= 'OK'
and pc.resp_1= 'OK'

In case you hadn't realized: `select distinct(x), y...` does not mean that `distinct` is applied only to `x`. It is not a function. `distict` modifies the whole result set so that it contains only unique rows. — Aleksi Yrttiaho, May 31 '11 at 18:03

score 2 · Answer 1 · answered May 31 '11 at 17:54

2

select entity, sum(overall)
from registry
group by entity
order by entity

your question is difficult to understand...

answered May 31 '11 at 17:54

Randy

16,480
1
37
55

i understand but my english is not good and more to explain this question, I have no column overall, I have to get the number of records per entity, this is the problem, thank you – ebing May 31 '11 at 18:32

score 2 · Accepted Answer · edited May 23 '17 at 12:12

Use a GROUP BY on the column that you want to be unique.
In order for PL-SQL to not complain you'll have to use an aggregate function that will select a value for the other columns.
I've picked MAX(), but MIN() or the non-existent whatever() will do just as well.

SELECT 
  rp.cod_entidad,
  count(*) as rowcount,   -- number of rows per distinct rp.cod_entidad
  MAX(to_date(to_char(SYSDATE,'YYYYMMDD'),'YYYY-MM-DD')) as fecha_pago,
  MAX(to_char(sysdate,'hh-mi-ss')) as hora_pago
FROM registry rp
INNER JOIN product pc ON (pc.nro_solicitud = rp.nro_solicitud)
WHERE pc.resp_2 = 'OK' AND pc.resp_1 = 'OK'
GROUP BY rp.cod_entidad

Implicit where join considered harmful
P.S. please don't use that ugly implicit where join, it is confusing and bad practise, because you are mixing selection criteria with join criteria in your where clause for no reason whatsoever.
We've had explicit join syntax since 1993, I warmly recommend it.
It will make writing correct queries much much easier and it will make your intentions clearer.

I'd guess the `YYYYYYY` was a placeholder for the solution and should be replaced with `count(*)`. — Aleksi Yrttiaho, Jun 01 '11 at 05:41

PL SQL : Query to count differents values of a field

2 Answers2