1

I have a table with 20 columns and each row can have a value of 0-4 (integer) in each column.

I would like to count the number of 0's, the number of 1's, the number of 2's etc in each column for a given subset of the table which is specified in the where clause.

A friend suggested PARTITION, but that's not supported in SQL Server CE. I suspect that I have to use count(expression) but I can't work out what the expression would be.

Can anyone give me a suggestion?

Thanks, Will.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3259859
  • 29
  • 6

1 Answers1

1

consider you have a table as below enter image description here

  Select min(subset.g), count(subset.val)
        from
        (
            Select 'c1 & '+cast(Val1 as varchar(4)) as G,
                            val1 as val
                from test
            Union all
                Select 'c2 & '+ cast(Val2 as varchar(4)) as G,
                       val2 as val
                from test   
            Union all
                Select 'c3 & '+ cast(Val3 as varchar(4)) as G,   
                   val3 as val
                from test   
        ) as subset
    Group by  Left(subset.g,2),subset.val
    Order by Left(subset.g,2)

the the result set would be like this

enter image description here

Hossein Salmanian
  • 753
  • 4
  • 14
  • thats close, thanks, but I need those totals for each column, not just for the whole table. – dontpanic Apr 29 '14 at 09:19
  • @dontpanic Sorry for misunderstanding i just update my answer. – Hossein Salmanian Apr 29 '14 at 11:08
  • Those results look just like what I am looking for, however I'm getting an error on the keyword 'Left' in the group by clause. I haven't found reference to that word in the SQL Compact reference so I am wondering if its not supported. Thanks. – dontpanic Apr 29 '14 at 12:45
  • yes, Left is not supported, but I am going to try using substring instead. – user3259859 Apr 29 '14 at 12:57
  • ok please tell me if it helped and don't forget to check my solution as answer ;) – Hossein Salmanian Apr 29 '14 at 13:04
  • I think I may have translated your code to mine incorrectly because my results are different. I have tested it for 2 columns (of the 20) and my column names are atmresulttype1 and atmresulttype2. I have 4 rows with values 4,4,1,4 (same for both columns). When I run my code (below) I only get 2 rows returned : C1&1,2 and C2&4,6. those totals are correct, but I should get 4 rows returned with C1&1,1 and C1&4,3 and C2&1,1 and C2&4,3. I will post my code as an answer (I dont know how to add it in this comment) – user3259859 Apr 29 '14 at 13:14
  • you changed the group name length from 2 to 13 so you must change the second parameter of the left or second parameter of substring function to 13 – Hossein Salmanian Apr 29 '14 at 13:16
  • Select min(subset.g), count(subset.atmresulttype) from (Select 'c1&'+cast(atmresulttype1 as nvarchar(4)) as G, atmresulttype1 as atmresulttype from results Union all Select 'c2&'+ cast(atmresulttype2 as nvarchar(4)) as G, atmresulttype2 as atmresulttype from results) as subset Group by substring(subset.g,0,2),subset.atmresulttype Order by substring(subset.g,0,2) – user3259859 Apr 29 '14 at 13:20
  • oh...silly... I will try that. I thought the value '2' was referring to the string length of 'C1' – user3259859 Apr 29 '14 at 13:21
  • you must change the third parameter of substring function like this substring(subset.g,0,13) – Hossein Salmanian Apr 29 '14 at 13:23
  • Great, that does work, thanks very much. Now all I have to do is go over the code and understand what it is doing! – user3259859 Apr 29 '14 at 13:24