CODEWITHSUNDEEP
sqlsql-server
Loupax
Loupax

Reputation: 4914

Count results with condition in the select part of the query

I need the amount of words that start from all the characters of the alphabet, exist in my database

I tried the following query:

SELECT 
    COUNT(id) FROM(SELECT id FROM words WHERE word LIKE 'A%') as A,
    COUNT(id) FROM(SELECT id FROM words WHERE word LIKE 'B%') AS B,
    ...
    COUNT(id) FROM(SELECT id FROM words WHERE word LIKE 'Z%') AS Z

When I run the query, it gives me this error:

Incorrect syntax near 'COUNT'.    
Incorrect syntax near the keyword 'AS'.

The funny thing is that the query works fine if I only ask for the words that start with 'A'

What am I doing wrong?

EDIT

Just to clarify for future readers. The reason I want one check per letter of the alphabet, is because the alphabet can be different according to the language of the user and is going to be provided each time the query is generated

Upvotes: 2

Views: 873

Answers (4)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

If you don't mind having the results in rows instead of columns this should work:

SELECT LEFT(word, 1) aChar, count(id) total
FROM words
WHERE word LIKE '[A-Z]%'
GROUP BY LEFT(word, 1)

Fiddle here.

Also, according to your comment:

I have to make all those checks, because the alphabet is going to be different, according to the language of the user

It makes much more sense to use a regular expression here as you can parameterise the value of the regular expression and add or remove characters without changing the code of the query (only the parameter).

Upvotes: 1

dpw
dpw

Reputation: 1586

You don't need to separate the COUNT operation from the subquery, you can do it inline.

SELECT
    (SELECT COUNT(*) FROM words WHERE word LIKE 'A%') AS A,
    (SELECT COUNT(*) FROM words WHERE word LIKE '%B') AS B,
    (SELECT COUNT(*) FROM words WHERE word LIKE '%C') AS C

Upvotes: 0

Andrew
Andrew

Reputation: 8693

I'd suggest sub-stringing out the first character:

select
substring(word,1,1) as firstChar,
count(id)
from...
group by
substring(word,1,1)

That seems easier than 26 individual checks.

Upvotes: 1

sahalMoidu
sahalMoidu

Reputation: 1152

USE CASE WHEN AS FOLLOWS

SELECT COUNT(CASE WHEN word LIKE 'A%' THEN 1 END) AS A
      ,COUNT(CASE WHEN word LIKE 'B%' THEN 1 END) AS B
.........
      ,COUNT(CASE WHEN word LIKE 'Z%' THEN 1 END) AS Z
FROM WORDS

Upvotes: 1

Related Questions