Using Group By and Order By Together When combining the Group By and Order By clauses, it is important to bear in mind that, in terms of placement within a SELECT statement: The GROUP BY clause is placed after the WHERE clause. The GROUP BY clause is placed before the ORDER BY clause.
Group by statement is used to group the rows that have the same value. Whereas Order by statement sort the result-set either in ascending or in descending order.
You can, but the "GROUP BY" clause is used for grouping together sets of rows, so it does not make sense for your question (or anything that involves a "SELECT *").
While the GROUP BY Clause groups rows that have the same values into summary rows. The having clause is used with the where clause in order to find rows with certain conditions. The having clause is always used after the group By clause.
Your query is wrong, since you would need to perform some aggregation function on EMPLOYEE_ID if you want that to work.
Like:
select substr(first_name,1,1) as alpha, count(employee_id)
from employees
group by substr(first_name,1,1)
What exactly you are trying to accomplish?
You'll need to group by everything that is not an aggregate function, so you can't have employee_id in the SELECT projection. You also need to group by just the first character of the first_name. Something like this should work:
SELECT SUBSTR(first_name, 1, 1) AS alpha, COUNT(*) AS employee_count
FROM employees
GROUP BY SUBSTR(first_name, 1, 1);
That would group by the first letter of the first name, and show the number of employees that fall into that group.
I have similar issue and solved it this with statement:
select SUBSTR(word, 1, 1) as S, count(word) FROM table_words group by S order by S ASC
It almost sounds like you want 26 records returned with A, B, C as the first column and then a second column containing all the employee IDs in a delimited list. If so see question 468990 and/or this Ask Tom link. Something like (untested)
SELECT SUBSTR(first_name,1,1), TO_STRING( CAST( COLLECT( employee_id ) AS ntt_varchar2 ) ) AS empIDs
FROM employees
GROUP BY
SUBSTR(first_name,1,1);
When you are grouping, all of the columns that appear in your select list that are not aggregated have to also appear in the "group by" clause (employee_id does not).
Could you clarify what it is you are trying to do?
In Rails/postgres that might look something like this
group_clause = 'UPPER(LEFT(name, 1))'
Division.group(group_clause).order(group_clause).pluck(group_clause, 'COUNT(id)')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With