CODEWITHSUNDEEP
mysqlgroup-by
Boomfelled
Boomfelled

Reputation: 535

Grouping an outcome in MySQL

I'm doing a simple MySQL Query and would like to use GROUP to give me a set of results for all of my locations rather than having to run the query uncommenting the various lines on each occasion. I'm confused about whether or not to use GROUP in the SELECT or WHERE clause.

SELECT COUNT(*) FROM
Users AS u
-- Northern Ireland
WHERE u.postcode_area IN ('BT')
-- WALES
-- WHERE u.postcode_area IN ('CF','CH','GL')
-- Scotland
-- WHERE u.postcode_area IN ('AB','DD','DG','EH','FK')

So as an outcome I'd like to see

Upvotes: 1

Views: 44

Answers (3)

nl-x
nl-x

Reputation: 11832

You can simply try it like this:

select 
  if(u.postcode_area in ('BT'), 'Northern Ireland', 
    if(u.postcode_area in ('CF','CH','GL'), 'Wales', 
      if(u.postcode_area in ('AB','DD','DG','EH','FK'), 'Scotland', 
        'Unknown'
      )
    )
  ) as label
  , count(*)
from Users as u
group by label

edit:

By the way, it would be neater to have a table containing postcode_area linked to label.

Northern Ireland | BT
Wales | CF
Wales | CH
Wales | GL
Scotland | AB
Scotland | DD
Scotland | DG
Scotland | EH
Scotland | FK

then your query would be:

select
  pl.label,
  count(*) as count
from
  Users as u
  inner join containing postcode_area_label as pl
    on u.postcode_area = pl.postcode_area
group by
  pl.label

Upvotes: 1

ılǝ
ılǝ

Reputation: 3528

This will give all three results at the same time:

SELECT ireland.ireland_count, wales.wales_count, scotland.scotland_count FROM
(SELECT COUNT(*) as ireland_count FROM Users WHERE postcode_area IN ('BT')) as ireland
JOIN
(SELECT COUNT(*) as wales_count FROM Users WHERE postcode_area IN ('CF','CH','GL')) as wales
JOIN
(SELECT COUNT(*) as scotland_count FROM Users WHERE postcode_area IN ('AB','DD','DG','EH','FK')) as scotland;

The output will be like:

ireland_count   |   wales_count    |   scotland_count
25678           |   34543          |   4567

Upvotes: 1

xdazz
xdazz

Reputation: 160833

You could do like below:

SELECT 
    (CASE
        WHEN u.postcode_area IN ('BT') THEN 'Northern Ireland'
        WHEN u.postcode_area IN ('CF','CH','GL') THEN 'WALES'
        WHEN u.postcode_area IN ('AB','DD','DG','EH','FK') THEN 'Scotland'
        ELSE 'other') AS country,
    COUNT(*) 
FROM
    Users AS u
GROUP BY country

Upvotes: 2

Related Questions