Last post Feb 10, 2016 01:29 AM by thoth
Feb 09, 2016 01:56 AM|thoth|LINK
Suppose I have a table with 4 columns labeled (Num, A, B, and C)
Columns A, B and C only contain letters (1 character in each row/column. How can I get a count of the occurrences of each letter in the alphabet in each column (A,B, and C) throughout a specified range to be determined by the where clause based on the Num
column.. I need zero counts if letters don;t exist as well.
Ideally the result set would look as follows
A B C
a 12 1 5
b 0 6 7
c 9 3 7
z 4 8 0
Should I create a table that contains the letters of the alphabet and use it with a join?
Feb 09, 2016 06:33 AM|TechView|LINK
Hello thoth!! I've made a simple sample for you that will work with 4 columns (Num [auto-increment], A, B, and C). Try it:
DECLARE @value as char = 'b'
SELECT @value AS Letter, ISNULL(COUNT(A),0) AS A,
ISNULL((SELECT COUNT(B) FROM Letters WHERE B = @value), 0) AS B,
ISNULL((SELECT COUNT(C) FROM Letters WHERE C = @value), 0) AS C
FROM Letters WHERE A = @value
Just you have to use sub query to obtain the required output. Hope this helps.
Feb 09, 2016 08:09 AM|sandeepmittal11|LINK
declare @tab table (A char(1), B char(1), C char(1))
insert into @tab
, sum(case when a.chr = b.A then 1 else 0 end) as A
, sum(case when a.chr = b.B then 1 else 0 end) as B
, sum(case when a.chr = b.C then 1 else 0 end) as C
select char(number) as chr
where type ='P' and number between 97 and 122
left join @tab b on (a.chr = b.A or a.chr = b.B or a.chr = C)
group by chr
Feb 10, 2016 12:50 AM|thoth|LINK
How would I accomplish this without using master.dbo.spt_values?
If I had a table simply containing one column with 26 rows, each row containing only one letter of the alphabet. How would I join the table of alphabet letters in the query to give the same result set as you provided?
Feb 10, 2016 01:29 AM|thoth|LINK
I modified it and got it working, thank you!