0

I was trying to do something like this: 

select nume_produs
from incasari 
group by id
having count(nume_produs) =  max(count(nume_produs));

but it doesn't work

APC
  • 144,005
  • 19
  • 170
  • 281
ioio79
  • 29
  • 1
  • 4
  • 2
    Please provide sample data and desired results. For instance, why are you aggregating by `id`? – Gordon Linoff May 20 '19 at 11:08
  • because it's the id of the nume_produs. I have the table Table with columns id, product_id, product_name (nume_produs), amount, price, data collection – ioio79 May 20 '19 at 11:12
  • and I need to write a function for the most sold product of the month – ioio79 May 20 '19 at 11:14

3 Answers3

3

Do a GROUP BY. Order by count descending. Fetch the first row (highest count) only.

select nume_produs, count(*) as cnt
from incasari 
group by nume_produs
order by cnt desc
fetch first 1 row with ties
jarlh
  • 42,561
  • 8
  • 45
  • 63
3

For the most common value in the column:

select num_produs
from (select nume_produs, count(*) as cnt,
             row_number() over (order by count(*)) as seqnum
      from incasari
      group by nume_produs
     ) i
where seqnum = 1;

If you want multiple values in the event of duplicates, use rank() instead of row_number().

If you want the most common value per id, then add partition by:

select num_produs
from (select nume_produs, count(*) as cnt,
             row_number() over (partition by id order by count(*)) as seqnum
      from incasari
      group by nume_produs
     ) i
where seqnum = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
-1
SELECT       `nume_produs`,
             COUNT(`nume_produs`) AS `value_occurrence` 
    FROM     `incasari`
    GROUP BY `nume_produs`
    ORDER BY `value_occurrence` DESC
    LIMIT    1;

Increase 1 if you want to see the N most common values of the column.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Srikanth J
  • 388
  • 2
  • 12
  • Oracle database does not work with `LIMIT` so this query will fail.. You would need to use ROWNUM in Oracle or ANSI/ISO SQL standard FETCH – Raymond Nijland May 20 '19 at 11:30
  • 1
    Backticks won't work with Oracle. Use ANSI SQL's double quotes instead, like `"nume_produs"`. – jarlh May 20 '19 at 11:54
  • @jarlh - except that using double-quotes with mixed-case identifiers will hurl ORA-00942 if the table was not created like that, which I'm guessing is the case for the OP. – APC May 20 '19 at 14:00
  • @srikanth - answers for questions tagged `[oracle]` should use Oracle RDBMS syntax, not MySQL (even though it's owned by ORCL corporation). Also not MS SQL Server syntax even when the question is tagged `[sql]`. – APC May 20 '19 at 14:02