Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

group by first character

People also ask

Which comes first ORDER BY or GROUP BY?

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.

What is GROUP BY and ORDER BY?

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.

Can we use * in GROUP BY?

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 *").

Can there be before GROUP BY?

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

output


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)')