Create enumeration and use it in aggregate function

Question

Is it possible to create some enumaration for 'a','b','test','123','blabla' in following statement?

sum(case when col1 in ('a','b','test','123','blabla') then col2 end) as sum

I've tried to read it from letters_table like this:

sum(case when col1 in (select letter from letters_table) then col2 end) as sum

but it told me Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

and following is not working fine for me:

DECLARE @letters varchar(10)
select @letters = letter FROM letters_table
sum(case when col1 in (@letters) then col2 end) as sum

because when I print the @letters, there is just the last one 'blabla'

Sum is a mathematical function and will not work on text. Perhaps you want to use a string one ... SQL server uses [STUFF](http://msdn.microsoft.com/en-us/library/ms188043.aspx) I believe. but may need to be used in concert with FOR XML Path to aggregate the rows. — xQbert, Aug 13 '14 at 13:44
It's not clear from your code what you're trying to do. What are the col1 and col2 you're referring to? What sum are you trying to get? — Patrick Tucci, Aug 13 '14 at 13:44
@xQbert: is't just for this example, it could be any string, not just a, b, c, d but e.g. test, tree, heart, 123 etc. — gaffcz, Aug 13 '14 at 13:46
@chipmunkofdoom2: http://stackoverflow.com/questions/25286039/how-to-sum-listed-values-in-column — gaffcz, Aug 13 '14 at 13:47
@gaffcz look at http://stackoverflow.com/questions/12559551/sql-server-equivalent-of-wm-concat-function — xQbert, Aug 13 '14 at 13:47

Joseph B · Answer 1 · 2014-08-14T11:59:38.547

3

If you have col1 (a text column) and col2 (a numeric column) in a table, say myTable and letter (a text column) in another table, say letters_table, then you can use a JOIN, as below:

SELECT 
    --mt.col1,        Include for totals by col1
    SUM(mt.col2)
FROM myTable mt
INNER JOIN letters_table lt
ON mt.col1 = lt.letter;
--    GROUP BY mt.col1    Include for totals by col1
--    ORDER BY mt.col1;   Include for totals by col1

edited Aug 14 '14 at 11:59

answered Aug 13 '14 at 13:48

Joseph B

5,519
1
15
19

this will return separate row for each word, which I believe is not what OP is asking for (based on his comments and [related question](http://stackoverflow.com/questions/25286039/how-to-sum-listed-values-in-column)) – kristof Aug 13 '14 at 15:09
@kristof I modified the query to get one total. This query should be faster as it uses a JOIN. – Joseph B Aug 14 '14 at 12:00

score 2 · Accepted Answer · answered Aug 13 '14 at 13:47

Your second example using the subquery is probably the best - you just need to do the subquery and aggregation in two separate phases, like this:

select sum(x.col2) as sum
from (
    select case 
           when col1 in (select letter from letters_table) then col2 
           else 0 
           end as col2
    from YourTableName
) x

This should work as intended, and make the parser happy.

score 2 · Answer 3 · answered Aug 13 '14 at 13:48

...or you could use a Common Table Expression, for example:

DECLARE @Test TABLE (col1 VARCHAR(1), col2 INT);
INSERT INTO @Test VALUES ('a', 1);
INSERT INTO @Test VALUES ('c', 20);
INSERT INTO @Test VALUES ('z', 3);
DECLARE @Letters TABLE (Letter VARCHAR(1));
INSERT INTO @Letters VALUES ('a');
INSERT INTO @Letters VALUES ('b');
INSERT INTO @Letters VALUES ('c');
WITH PreQuery AS (
    SELECT
        col1,
        CASE WHEN l.Letter IS NULL THEN 0 ELSE 1 END AS GoodLetter,
        col2
    FROM
        @Test t
        LEFT JOIN @Letters l ON l.Letter = t.col1)
SELECT 
    SUM(CASE WHEN GoodLetter = 1 THEN col2 END) 
FROM 
    PreQuery;

score 1 · Answer 4 · edited May 23 '17 at 10:26

The solution posted by Richard should do the trick, but there is no need to use cte you can simply use the code below

select
   isnull(sum(mt.col2),0)
from 
  myTbl mt
  inner join lettersTbl lt
    on mt.col1 = lt.letter

If you want to include the letters involved in the results, you may use a variable. Looking at your previous post here is a code with the same sample data that you have provided

declare @yourTbl table (col1 char(1), col2 int);
insert into @yourTbl values ('a', 10)
,('b', 5)
,('c', 15)
,('d', 2)
,('a', 3)
,('b', 6) 
,('c', 8) 
,('d', 10);

declare @lettersTbl table (letter char(1));
-- insert your letters here
insert into @lettersTbl values ('a'),('b')


-- only needed if you want to display the letters involved too
declare @checkedLetters as varchar(50)
select @checkedLetters = concat(@checkedletters,lt.letter)
from 
    @letterstbl lt

select
    @checkedletters 
   ,isnull(sum(mt.col2),0)
from 
    @yourtbl mt
    inner join @letterstbl lt
      on mt.col1 = lt.letter

Create enumeration and use it in aggregate function

4 Answers4