GROUP BY a computed column

[The Quiet Center]

If I want to count the number of records in a table GROUPed BY the
first word in a string column, must I first compute the first word and
store in a temporary table and then do my GROUP BY select?

[Plamen Ratchev]

Re: GROUP BY a computed column

You can do this in a single query grouping by the expression extracting the
first word from the string:

CREATE TABLE Foo (
keycol INT PRIMARY KEY,
datacol VARCHAR(35));

INSERT INTO Foo VALUES(1, 'one way');
INSERT INTO Foo VALUES(2, 'one day');
INSERT INTO Foo VALUES(3, 'next day');

SELECT LEFT(datacol, CHARINDEX(' ', datacol) - 1) AS first_word,
COUNT(*) AS cnt
FROM Foo
GROUP BY LEFT(datacol, CHARINDEX(' ', datacol) - 1);


HTH,

Plamen Ratchev

http://www.SQLStudio.com

[--CELKO--]

Re: GROUP BY a computed column

CREATE TABLE Phrases
(phrase_id INTEGER NOT NULL PRIMARY KEY,
phrase_txt VARCHAR(500) NOT NULL);

SELECT start_word, COUNT(*) AS start_word_cnt
FROM (SELECT phrase_id, SUBSTRING (phrase_txt, 1, CHARINDEX(' ',
phrase_txt) - 1)
FROM Phrases)
AS FirstWords (phrase_id, start_word)
GROUP BY start_word;

[Alex Kuznetsov]

Re: GROUP BY a computed column

On Jun 26, 3:12 pm, The Quiet Center <thequietcen... @gmail.comwrote :

If I want to count the number of records in a table GROUPed BY the
first word in a string column, must I first compute the first word and
store in a temporary table and then do my GROUP BY select?

If you do that frequently, consider creating an index on a computed
column.