How do I Count() totals across multiple fields?

[originalclassic]

In my data table i have six columns(A-F below) that i need to get count totals from. Each cell contains one of 20 preset values(1-20below). The first column always has one of the values, but each additional column may or may not depending on the complexity of the original entry. I am trying to run a report that will count all of the occurences of each of the values (regardless of which column they occur in) and then group the results by one of the other fields (contained in a 7th column of the same table). The example table below should return a count of 3 for value6. Using AND in the WHERE statement only returns a count if all six columns have the same value (never) and OR does not account for the entries that have the same value in more than one column.


A B C D E F
9
2 13 14
3 3 6
6 7 20 6 11 10
16
16 17
4

 

[tlbroadbent]

Here is one possible solution involving a union sub-query.

Select Val, G, Cnt=Sum(Cnt) From (
Select Val=a, g, Cnt=count(a)
From #t
Where a is not null
Group By a,g
Union All
Select b, g, Cnt=count(b)
From #t
Where b is not null
Group By b,g
Union All
Select c, g, Cnt=count(c)
From #t
Where c is not null
Group By c,g
Union All
Select d, g, Cnt=count(d)
From #t
Where d is not null
Group By d,g
Union All
Select e, g, Cnt=count(e)
From #t
Where e is not null
Group By e,g
Union All
Select f, g, Cnt=count(f)
From #t
Where f is not null
Group By f,g) As sq
group by val, G
order by 1,2 Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[originalclassic]

Terry,
Thanks for the quick response. I tried playing with the code that you posted for me, but it seems like i did not give you enough information from the beginning. In the end, the report we want to generate should be a large table that lists the values of the 7th column down the left side (in your code you called them g, in the real data they are the names of sales people). then there will be a column for each of the 20 different values. so one line of the report will read across as: name of salesperson | number of value1 logged by that person(as counted across all six columns of the table and multiple entries by the person) | number of value2 | number of value3 |...

perhaps i misunderstood some part of your explanation?

thanks again

Alex Husted

 

[tlbroadbent]

The result of the query I provided would be a vertical summary by sales person.
[tt]
Val SalesPerson Cnt
1 Mary 5
2 Mary 2
3 Mary 7
4 Mary 1
6 Mary 1
11 Mary 2
.
.
.
1 Ted 2
3 Ted 3
5 Ted 1
6 Ted 6
11 Ted 3
16 Ted 5
.
.
.
etc.

What you want is a cross-tab query which T-SQL doesn't provide. However, you can emulate a cross-tab query. It is not overly complex but the SQL script will be lengthy.

First modify the query I provided to use the names from your table. For example, change G to SalesPerson. Then create a view from that query. I'll assume the view is named vSalesPersonSummary.

Now create another query like the following.

Select
SalesPerson,
sum(Case Val When 1 Then Cnt Else 0 End) As CntVal1
sum(Case Val When 2 Then Cnt Else 0 End) As CntVal2
sum(Case Val When 3 Then Cnt Else 0 End) As CntVal3
.
. <fill in with code the remainder of the values>
.
sum(Case Val When 19 Then Cnt Else 0 End) As CntVal19
sum(Case Val When 20 Then Cnt Else 0 End) As CntVal20
From vSalesPersonSummary

This will return one row per SalesPerson with the count of value 1 to 20. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[originalclassic]

Terry,
I got the first piece of code working beautifully, but I am having trouble with the second. I did everything as you said (and it is quite a sneaky little bit of code by the way) but MS SQL keeps giving me errors on the SUM function.
I have mine set up like this: sum(Case Val When '10 Damaged in Shipping' Then Cnt Else 0 End) As Code10

I am doing all the SQL testing right in the Enterprise Manager, but the final pages will be in ColdFusion. I could not find any examples that would help me figure out what might be wrong with the syntax of this complex SUM.

If you could help me out one more time that would be fantastic.

thanks again

Alex

 

[tlbroadbent]

Alex,

Could you post the entire query and the error message?

Thanks, Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.