Hi, I’m using the following query in MySQL → (fig.1) which returns the result in fig.2. The first_letter column is the first letter of the heading in that row.
What I’m trying to get is a 3rd column which counts the total number of headings that begin with the same letter - (including the current one). eg. A=6, B=9, C=6, this continues until z. Some will have 0.
Is there a way that I can create a third column to do this? I’ve been trying this for a while and I’m pretty confused. Would I need some sort of subquery?
FIG.1
SELECT DISTINCT(h.heading) AS distinct_heading,
LEFT(h.heading,1) AS first_letter
FROM headings h
INNER JOIN TOWN_NAME t
ON h.heading=t.heading
WHERE t.Category = 'Classified'
ORDER BY h.heading ASC
FIG.2
+-------------------------+--------------+--+
| distinct_heading | first_letter | |
+-------------------------+--------------+--+
| Accountants | A | |
| Adult Education | A | |
| Aerials | A | |
| Alarms | A | |
| Architectural Services | A | |
| Art & Craft | A | |
| Bathrooms | B | |
| Beauty Salons & Therapy | B | |
| Bed & Breakfast | B | |
| Bedrooms | B | |
| Boiler Maintenance | B | |
| Bookkeeping Services | B | |
| Builders | B | |
| Builders Merchants | B | |
| Building Plans | B | |
| Car & Van Hire | C | |
| Car Body Repairs | C | |
| Car Maintenance | C | |
| Car Parts & Accessories | C | |
| Car Sales | C | |
| Caravans & Motorhomes | C | |
+-------------------------+--------------+--+
This is the result I’m trying to get:
+-------------------------+--------------+--+
| distinct_heading | first_letter | letter_count |
+-------------------------+--------------+--+
| Accountants | A | 6 |
| Adult Education | A | 6 |
| Aerials | A | 6 |
| Alarms | A | 6 |
| Architectural Services | A | 6 |
| Art & Craft | A | 6 |
| Bathrooms | B | 6 |
| Beauty Salons & Therapy | B | 9 |
| Bed & Breakfast | B | 9 |
| Bedrooms | B | 9 |
| Boiler Maintenance | B | 9 |
| Bookkeeping Services | B | 9 |
| Builders | B | 9 |
| Builders Merchants | B | 9 |
| Building Plans | B | 9 |
| Car & Van Hire | C | 6 |
| Car Body Repairs | C | 6 |
| Car Maintenance | C | 6 |
| Car Parts & Accessories | C | 6 |
| Car Sales | C | 6 |
| Caravans & Motorhomes | C | 6 |
+-------------------------+--------------+--+
first of all, DISTINCT is ~not~ a function, so you shouldn’t be putting the first column that comes after it into parentheses
second, you are on the right track with a LEFT OUTER JOIN, but since you say “some will have 0” then the left table should be a table of all the letters…
third, i find it disconcerting that you include town_name in the query, when you actually aren’t using it at all, so i’m going to drop it for now… if we need to put it back later, i’ll help you do that
okay, here you go…
SELECT subquery.first_letter
, subquery.3rd_column
, headings.heading
FROM ( SELECT letters.first_letter
, COUNT(LEFT(headings.heading,1)) AS 3rd_column
FROM ( SELECT 'A' AS first_letter
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
UNION ALL SELECT 'F'
UNION ALL SELECT 'G'
UNION ALL SELECT 'H'
UNION ALL SELECT 'I'
UNION ALL SELECT 'J'
UNION ALL SELECT 'K'
UNION ALL SELECT 'L'
UNION ALL SELECT 'M'
UNION ALL SELECT 'N'
UNION ALL SELECT 'O'
UNION ALL SELECT 'P'
UNION ALL SELECT 'Q'
UNION ALL SELECT 'R'
UNION ALL SELECT 'S'
UNION ALL SELECT 'T'
UNION ALL SELECT 'U'
UNION ALL SELECT 'V'
UNION ALL SELECT 'W'
UNION ALL SELECT 'X'
UNION ALL SELECT 'Y'
UNION ALL SELECT 'Z' ) AS letters
LEFT OUTER
JOIN headings
ON LEFT(headings.heading,1) = letters.first_letter
GROUP
BY letters.first_letter ) AS subquery
LEFT OUTER
JOIN headings
ON LEFT(headings.heading,1) = subquery.first_letter
ORDER
BY subquery.first_letter
, headings.heading
Thanks for the DISTINCT reminder. I keep slipping into that mistake.
Great that you picked up on the letter tables and needing the 0’s. I
didn’t know how to get a ‘table’ of all the letters in a query, but I
do now, thanks. In some past experiments I had an actual table of letters, but
your example is much tidier.
Yes the TOWN_NAME table is a confusing one to have in the query but I’m pretty sure I need it, or at least I need to reference this table somehow. You can see that I have WHERE t.Category = ‘Classified’ - this controls what DISTINCT_HEADING I have appearing in my example above. If I didn’t have that then I would get a lot more headings that I didn’t need. I guess this means that I need it put back in, where’s the best place for it?
When I try running your query I get
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation ‘=’
which I’m struggling to troubleshoot.
Is this down to me needing to retype the apostrophes or a similar character? All the tables I can see are latin1_swedish_ci (not sure why though!) but anyway I can’t see any utf8mb4_general_ci - to fix this do I need to find a column which is utf8mb4_general_ci and change it to latin1_swedish_ci or is there a better way?
there are two LEFT OUTER JOINs in the query i gave you
replace them with these –
LEFT OUTER
JOIN ( SELECT headings.heading
FROM town_name
INNER
JOIN headings
ON headings.heading = town_name.heading
WHERE town_name.category = 'Classified' ) AS h
ON LEFT(h.heading,1) = letters.first_letter
LEFT OUTER
JOIN ( SELECT headings.heading
FROM town_name
INNER
JOIN headings
ON headings.heading = town_name.heading
WHERE town_name.category = 'Classified' ) AS h
ON LEFT(h.heading,1) = subquery.first_letter
#1054 - Unknown column 'headings.heading' in 'field list'
But there is definitely a column called ‘heading’ in the headings table. When I’ve tested it, it looks like the problem is with the 2 references to headings.heading in the first 5 lines of the query. The sub queries seem to work when they refer to headings.heading - I guess I need to make reference to FROM headings somewhere outside of the sub queries?
Here’s the entire query:
SELECT subquery.first_letter
, subquery.3rd_column
, headings.heading
FROM ( SELECT letters.first_letter
, COUNT(LEFT(headings.heading,1)) AS 3rd_column
FROM ( SELECT 'A' AS first_letter
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
UNION ALL SELECT 'F'
UNION ALL SELECT 'G'
UNION ALL SELECT 'H'
UNION ALL SELECT 'I'
UNION ALL SELECT 'J'
UNION ALL SELECT 'K'
UNION ALL SELECT 'L'
UNION ALL SELECT 'M'
UNION ALL SELECT 'N'
UNION ALL SELECT 'O'
UNION ALL SELECT 'P'
UNION ALL SELECT 'Q'
UNION ALL SELECT 'R'
UNION ALL SELECT 'S'
UNION ALL SELECT 'T'
UNION ALL SELECT 'U'
UNION ALL SELECT 'V'
UNION ALL SELECT 'W'
UNION ALL SELECT 'X'
UNION ALL SELECT 'Y'
UNION ALL SELECT 'Z' ) AS letters
LEFT OUTER
JOIN ( SELECT headings.heading
FROM town_name
INNER
JOIN headings
ON headings.heading = town_name.heading
WHERE town_name.category = 'Classified' ) AS h
ON LEFT(h.heading,1) = letters.first_letter
GROUP
BY letters.first_letter ) AS subquery
LEFT OUTER
JOIN ( SELECT headings.heading
FROM town_name
INNER
JOIN headings
ON headings.heading = town_name.heading
WHERE town_name.category = 'Classified' ) AS h
ON LEFT(h.heading,1) = subquery.first_letter
ORDER
BY subquery.first_letter
, headings.heading
SELECT subquery.first_letter
, subquery.3rd_column
, h.heading -- correction here
FROM ( SELECT letters.first_letter
, COUNT(LEFT(headings.heading,1)) AS 3rd_column
FROM ( SELECT 'A' AS first_letter
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
UNION ALL SELECT 'F'
UNION ALL SELECT 'G'
UNION ALL SELECT 'H'
UNION ALL SELECT 'I'
UNION ALL SELECT 'J'
UNION ALL SELECT 'K'
UNION ALL SELECT 'L'
UNION ALL SELECT 'M'
UNION ALL SELECT 'N'
UNION ALL SELECT 'O'
UNION ALL SELECT 'P'
UNION ALL SELECT 'Q'
UNION ALL SELECT 'R'
UNION ALL SELECT 'S'
UNION ALL SELECT 'T'
UNION ALL SELECT 'U'
UNION ALL SELECT 'V'
UNION ALL SELECT 'W'
UNION ALL SELECT 'X'
UNION ALL SELECT 'Y'
UNION ALL SELECT 'Z' ) AS letters
LEFT OUTER
JOIN ( SELECT headings.heading
FROM town_name
INNER
JOIN headings
ON headings.heading = town_name.heading
WHERE town_name.category = 'Classified' ) AS h
ON LEFT(h.heading,1) = letters.first_letter
GROUP
BY letters.first_letter ) AS subquery
LEFT OUTER
JOIN ( SELECT headings.heading
FROM town_name
INNER
JOIN headings
ON headings.heading = town_name.heading
WHERE town_name.category = 'Classified' ) AS h
ON LEFT(h.heading,1) = subquery.first_letter
ORDER
BY subquery.first_letter
, h.heading -- correction here
Thanks r937 - much appreciated! It all works brilliantly. Clever stuff, I’m getting my head around it!
And for anyone else, regarding the collation issue, I changed the collation to utf8_unicode_ci for all relevant tables and columns and it worked - not sure why they weren’t utf8_unicode_ci in the first place.