SQL - select most frequent value

Question

Im stuck with this, I hope someone can help me with this:

SELECT max(SELECT count(TO_CHAR(hire_date, 'DAY')) 
           FROM employees 
           GROUP BY TO_CHAR(hire_date, 'DAY')) 
FROM employees;

           *
ERROR at line 1:
ORA-00936: missing expression

The output of the subquery looks like that:

TO_CHAR(HIRE_DATE,'DAY')         COUNT(TO_CHAR(HIRE_DATE,'DAY'))
---------------------------------------- -------------------------------
THURSDAY                                   3
SATURDAY                                   3
WEDNESDAY                                  4
MONDAY                                     1
SUNDAY                                     3
TUESDAY                                    6

I just want to select TUESDAY

**[edit]** your question and add some sample data and the expected output based on that data. [**Formatted text**](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). Do not post code or additional information in comments — , Jun 10 '17 at 20:20
What do you want the result to be if Tuesday and Friday (let's say) are tied for the most hires (like 6)? — , Jun 10 '17 at 20:32
The best would be if both results get returned but first just focus on this example if this doesn't work — maik mayo2, Jun 10 '17 at 20:34
Please add DML to show creating the table and adding in some sample data. — Keith John Hutchison, Jun 10 '17 at 20:42
@KeithJohnHutchison - the data doesn't match what's in the HR schema, but the structure (column names, table name) seem to match `HR.EMPLOYEES` exactly. That's what I used for testing my answer. — , Jun 10 '17 at 20:45
@KeithJohnHutchison - I could, but I am not sure that would be legal. The HR schema is owned by Oracle (and DDL and DML statements to re-create it are available freely from Oracle). Moreover, almost all installations of Oracle come with the HR schema pre-installed; very likely you have it on your system already. — , Jun 10 '17 at 20:54
I don't have oracle on my system. I'm getting strange syntax errors in SQL Fiddle using create table statements. Which is why I'm asking for an example. I work mainly in MySQL, MSSQL and PostgresSQL. Changing my answer for the edited question is trivial if I can test. — Keith John Hutchison, Jun 10 '17 at 21:00
How should ties be handled? If for 6 employees the hire_date was Monday . what should be the result? — miracle173, Jun 11 '17 at 05:00
Please write down the expected result set. To say 'I just want to select TUESDAY' isn't clear enough. (look at the comments to [this answer](https://stackoverflow.com/a/44477799/754550)) — miracle173, Jun 11 '17 at 05:24

score 1 · Answer 1 · 2017-06-10T21:38:34.507

You can do this by adding the analytic function RANK() to the SELECT clause of the inner query. Then in the outer query select the rows where the rank is 1. This will produce all the "tied-for-first" days (either a single winner or tied for first place).

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions123.htm (look for the analytic version!)

That to_char(....) looks ugly, but since it is deterministic, Oracle computes it just once (even though it is used in four places).

select dy, ct
from   (
         select   to_char(hire_date, 'DAY') as dy,
                  count(to_char(hire_date, 'DAY')) as ct,
                  rank() over (order by count(to_char(hire_date, 'DAY')) desc) as rk
         from     hr.employees
         group by to_char(hire_date, 'DAY')
       )
where  rk = 1
;

Sorry but this returns that monday with 10 is the most frequent — maik mayo2, Jun 10 '17 at 21:06
@maikmayo2 - Then you **are**, indeed, using the standard `HR` schema; I got the same answer. I made one mistake: the `RANK()` function should be ordered by `count` **descending**. I just edited the answer to fix that. — , Jun 10 '17 at 21:35

Keith John Hutchison · Answer 2 · 2017-06-10T20:40:34.090

0

Your subquery in the question will not produce the output described. Try using a subquery that does.

select max( total ) from (
    SELECT TO_CHAR(HIRE_DATE,'DAY') day 
    , count(TO_CHAR(hire_date, 'DAY')) total
    FROM employees 
    GROUP BY TO_CHAR(hire_date, 'DAY')
) daysTotals

Untested on oracle.

edited Jun 10 '17 at 20:40

answered Jun 10 '17 at 20:34

Keith John Hutchison

4,955
11
46
64

1

@maikmayo2 - "This worked" is odd, since it only produces the highest total, but it does not produce which day had that total. – Jun 10 '17 at 20:44
I answered the original question ... there was no requirement for the day at that stage. – Keith John Hutchison Jun 10 '17 at 20:48
1

@KeithJohnHutchison - I addressed the question to the OP, who knows what his requirement is (one hopes!) – Jun 10 '17 at 21:40
@mathguy It looks like the OP is looking for the day(s) that people are hired the most. – Keith John Hutchison Jun 10 '17 at 21:48

score 0 · Answer 3 · answered Jun 10 '17 at 20:41

You could solve this with a subquery in a HAVING-clause:

select TO_CHAR(hire_date, 'DAY') as day, count(TO_CHAR(hire_date, 'DAY')) as cnt
FROM employees 
GROUP BY TO_CHAR(hire_date, 'DAY')) 
HAVING count(TO_CHAR(hire_date, 'DAY')) >= ALL (
           SELECT count(TO_CHAR(hire_date, 'DAY')) 
           FROM employees 
           GROUP BY TO_CHAR(hire_date, 'DAY')

score 0 · Answer 4 · answered Jun 10 '17 at 21:20

Try this .. the idea is to use an inner inner join to get all days with the maximum number of hires.

select hiredays.* from (
    select max( total ) total from (
        SELECT TO_CHAR(HIRE_DATE,'DAY') day 
        , count(TO_CHAR(hire_date, 'DAY')) total
        FROM employees 
        GROUP BY TO_CHAR(hire_date, 'DAY')) 
    ) daysTotals
) maximum
inner join (
    SELECT TO_CHAR(HIRE_DATE,'DAY') day 
    , count(TO_CHAR(hire_date, 'DAY')) total
    FROM employees 
    GROUP BY TO_CHAR(hire_date, 'DAY'))
) hiredays
on hiredays.total = maximum.total

Untested on Oracle.

SandPiper · Answer 5 · 2017-06-11T14:57:43.783

0

Use a subquery to find the unique hiring days of the week and the count associated. Then, use a RANK function to determine which one is the top. Then, select that day with the top rank.

WITH HireSummary AS (
     SELECT TO_CHAR(hire_date, 'DAY') AS HireDay, 
            COUNT(TO_CHAR(hire_date, 'DAY')) AS HireDayCount 
     FROM employees 
     GROUP BY TO_CHAR(hire_date, 'DAY')),
RankedSummary AS (
     SELECT HireDay, HireDayCount, RANK() OVER (ORDER BY HireDayCount DESC) AS DayRank
     FROM HireSummary)
SELECT HireDay FROM RankedSummary WHERE DayRank = 1;

The advantage of this is if you have hired 6 people on both Tuesday AND Wednesday, it will return both.

However, if you ONLY want a SINGLE answer for the above case, turn SELECT HireDay into SELECT MAX(HireDay).

edited Jun 11 '17 at 14:57

answered Jun 11 '17 at 00:26

SandPiper

2,816
5
30
52

you are wrong about the reason for the error. I wrote about this in [my answer](https://stackoverflow.com/a/44478243/754550)- The similar query `SELECT (SELECT count(TO_CHAR(hire_date, 'DAY')) FROM employees) FROM employees;` works (as expected) and also contains `count(TO_CHAR(hire_date, 'DAY'))` – miracle173 Jun 11 '17 at 04:30
Which one? The query in my comment? Yes, it works. The OPs throws this compile time error because an additional pair of parenthesis is missing. If you add these paranthesis it will compile but a run time error will be thrown, because only a single row query is allowed in a select list. It would be fine you remove the first sentence because it is wrong. – miracle173 Jun 11 '17 at 14:02
I see that missing parenthesis now. I took out the assessment. I see no reason the query I edited for him should not work though. – SandPiper Jun 11 '17 at 15:01

Wernfried Domscheit · Answer 6 · 2017-06-12T07:30:41.257

0

You can do it even without sub-query when you use FIRST expression:

SELECT 
    MAX(COUNT(hire_date)) KEEP (DENSE_RANK LAST ORDER BY COUNT(hire_date)),
    MAX(TO_CHAR(hire_date, 'DAY')) KEEP (DENSE_RANK LAST ORDER BY COUNT(hire_date))
FROM EMPLOYEES
GROUP BY TO_CHAR(hire_date, 'DAY');

But note, this returns only one day. In case you have several days with the same amount of employees you would see only one of them.

edited Jun 12 '17 at 07:30

answered Jun 12 '17 at 07:18

Wernfried Domscheit

54,457
9
76
110

miracle173 · Answer 7 · 2017-06-11T10:20:45.903

your query does not make sense, but you describe what you want and there are already answers that answer this question. I think you wanted to do something like

SELECT max(empcount)
from (SELECT count(TO_CHAR(hire_date, 'DAY'))  empcount
    FROM employees 
    GROUP BY TO_CHAR(hire_date, 'DAY')) 
;

which would return

I will not try to write an appropriate select statement here but concentrate on the error message.

You use a select statement in your select list. This is called a scalar subquery expresion. The Database SQL Language Reference, subsection: Scalar Subquery Expressions says:

You can use a scalar subquery expression in most syntax that calls for an expression (expr). In all cases, a scalar subquery must be enclosed in its own parentheses, even if its syntactic location already positions it within parentheses (for example, when the scalar subquery is used as the argument to a built-in function).

So the error message

ORA-00936: missing expression

is correct. After max( the parser is expecting an expression and not the keyword select. So you have to add an additional pair of parenthesis: one for the max function and one for the select statement: But now he statement

SELECT max((SELECT count(TO_CHAR(hire_date, 'DAY')) 
    FROM employees 
    GROUP BY TO_CHAR(hire_date, 'DAY')))
    FROM employees;

raises

ORA-01427: single-row subquery returns more than one row

as expected, because your subselect returns more than one row.

(Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit)

SQL - select most frequent value

7 Answers7