Can I “GROUP BY” a partial field value in Access/SQL?
Clash Royale CLAN TAG#URR8PPP
Can I “GROUP BY” a partial field value in Access/SQL?
In Access/SQL, I know I can GROUP BY ,say, country or age.
GROUP BY
country
age
Can I also GROUP BY partial value, for example, field start with something like "G" or "18", so that "Germany" and "Ghana" are in one group, "1897" and "1870" are in one group?
GROUP BY
"G"
"18"
"Germany"
"Ghana"
"1897"
"1870"
GROUP BY LEFT(country, 1)
country
"G"
3 Answers
3
You can group by any non-aggregate expression. E.g.:
SELECT LEFT(country, 1), COUNT(*)
FROM mytable
GROUP BY LEFT(country, 1)
You can use MID() or LEFT() along with GROUP BY clause :
MID()
LEFT()
GROUP BY
select mid(country,1,1), count(*)
from table t
group by mid(country,1,1); -- or with left (country,1)
select group_concat(country), count(*) as count
from employees
group by left(country,1)
i think this should solve
Answer looks like
India, Indonesia 2
America, Australia, Argentina 3
There's no
group_concat in Microsoft Access...– Erik von Asmuth
Aug 7 at 18:00
group_concat
oh I understood it wrongly that "/" i thought he may asking about access or SQL
– user2873552
Aug 13 at 9:24
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
You can do
GROUP BY LEFT(country, 1)but you can't havecountryin the output since there is no way to aggregate multiple"G"countries into a single field in your output. Perhaps share some sample data and your desired results and we can offer more help here.– JNevill
Aug 7 at 17:26