Geek Answers Handbook

SQL SELECT: create a column that counts how many sub_categories begin with a specific letter

Below is the query I am using. I need a 4th column in my result, called letter_count, which counts how many sub_categories start with A, how many start with B, etc. Up to Z - it would be preferable if it could be done dynamically rather than adding a line for each letter.

The example table / result I'm looking for is displayed at the bottom of this question. I cannot decide how to modify the query to get this 4th column.

SELECT 
     headings.heading AS sub_category, 
    LEFT( headings.heading, 1 ) AS first_letter, 
    headings.url_code as url_code 
FROM TOWN_TABLE a 
    INNER JOIN headings ON a.Heading=headings.heading 
WHERE Category = 'Classified'
GROUP BY sub_category
ORDER BY sub_category ASC

Result:

+-------------------------+--------------+-------------------------+--+
|      sub_category       | first_letter |        url_code         | letter_count |
+-------------------------+--------------+-------------------------+--+
| Accountants             | A            | accountants             | 6 |
| Adult Education         | A            | adult education         | 6 |
| Aerials                 | A            | aerials                 | 6 |
| Alarms                  | A            | alarms                  | 6 |
| Architectural Services  | A            | architectural services  | 6 |
| Art & Craft             | A            | art and craft           | 6 |
| Bathrooms               | B            | bathrooms               | 8 |
| Beauty Salons & Therapy | B            | beauty salons & therapy | 8 |
| Bed & Breakfast         | B            | bed and breakfast       | 8 |
| Bedrooms                | B            | bedrooms                | 8 |
| Boiler Maintenance      | B            | boiler maintenance      | 8 |
| Bookkeeping Services    | B            | bookkeeping services    | 8 |
| Builders                | B            | builders                | 8 |
| Builders Merchants      | B            | builders merchants      | 8 |
+-------------------------+--------------+-------------------------+--+
+4
source share
3 answers

, , , . , , headings . , WHERE , , WHERE .

SELECT headings.heading AS sub_category, 
       LEFT( headings.heading, 1 ) AS first_letter, 
       headings.url_code as url_code , 
       letter_counts.letter_count
FROM headings ON a.Heading=headings.heading 
INNER JOIN ( 
      -- make a derived table of each letter and how many 
      -- headings start with it. Only count ones that have
      -- a TOWN_TABLE entry with category Classified.
      select left(h_all.heading,1) as the_letter, 
             count(*) as letter_count
      from headings h_all
      WHERE EXISTS ( select * from TOWN_TABLE t 
                     where t.heading = h_all.heading 
                     AND Category = 'Classified') 
      group by left(h_all.heading,1)
    ) as letter_counts on left(heading.heading,1) = letter_counts.the_letter
WHERE EXISTS ( select * from TOWN_TABLE t 
               where t.heading = headings.heading 
               AND Category = 'Classified') 
order by headings.heading

SQL Server CTE, . , CTE mysql:

with sub_categories ( first_letter, sub_category ) 
as ( 
     select left( h.heading, 1 ) 
          , h.heading
     from headings h
     where exists( select * from TOWN_TABLE t 
                    where t.heading = h_all.heading 
                    and Category = 'Classified') 
) 
select sc.sub_category
     , sc.first_letter
     , sub_category_counts.the_count
from sub_categories sc
inner join ( 
    select first_letter
         , count(*) as the_count
    from sub_categories
    group by first_letter
) as sub_category_counts on sub_category_counts.first_letter = sc.first_letter
order by sc.sub_category
+1

, :

SELECT 
     headings.heading AS sub_category, 
    LEFT( headings.heading, 1 ) AS first_letter, 
    headings.url_code as url_code , CNT as letter_count
FROM TOWN_TABLE a 
    INNER JOIN headings ON a.Heading=headings.heading 
INNER JOIN (SELECT LEFT( headings.heading, 1 ) AS first_letter, COUNT(DISTINCT LEFT( headings.heading, 1 )) as CNT FROM headings) AS lettercounts
ON lettercounts.first_letter = LEFT( headings.heading, 1 )
WHERE Category = 'Classified'
GROUP BY sub_category
ORDER BY sub_category ASC;
+1

:

SELECT
    ...,
    ( SELECT COUNT(*) FROM headings AS lett 
          WHERE LEFT(lett.heading, 1)=first_letter)
    AS letter_count
FROM ...

first_letter , . , first_letter , :

    ( SELECT COUNT(*) FROM headings AS lett 
          WHERE LEFT(lett.heading, 1)=LEFT(headings.heading, 1))
    AS letter_count

You use headingstwice, once to get the necessary data, a second time to get the first letter.

This is a link to the SQL script for testing :

SUB_CATEGORY    FIRST_LETTER    URL_CODE            LETTER_COUNT
Accountants     A               /url/accountants    2
Art & Craft     A               /url/arts_crafts    2
Bathrooms       B               /url/bathrooms      1
+1
source

More articles:


All Articles