Calculate Two Columns

That gives me a value of 100 then when I drop the *100 I get a value of 1

Try this :

SELECT AName, CAST(Count(TotalA) AS DECIMAL(10,2))/(CAST(Count(TotalB) AS DECIMAL(10,2))*100) AS GrandTotal FROM DATA

                                              

1:

try this query

SELECT AName, (Count(CAST(TotalA AS INT))/Count(CAST(TotalB AS INT)))*100 AS GrandTotal FROM DATA

You shoud get 0.01

Are you sure you want to count number of non null value from TotalA and TotalB and not doing the sum of TotalA and sum of TotalB ?

In this case :

SELECT AName, Sum(CAST(TotalA AS DECIMAL(10,2)))/(Sum(CAST(TotalB) AS DECIMAL(10,2)))*100) AS GrandTotal FROM DATA
                                              

1:

if you want sum then use following

SELECT AName, (sum(CAST(TotalA AS INT))/sum(CAST(TotalB AS INT)))*100 AS GrandTotal FROM DATA
group by AName

>>That gives me a value of 100 then when I drop the *100 I get a value of 1<<
As it should.  Unless there are Nulls Count(TotalA) = Count(TotalB)

this may help:

SELECT AName, CAST(Count(isnull(TotalA,0)) AS INT)/CAST(Count(isnull(TotalB,0)) AS INT)*100 AS GrandTotal FROM DATA

No matter which one I try I get a cannnot convert nvarchar to numeric error message

what error message do you get? I just created sample data and working fine, look at that.

create table DATA
(
TotalA nvarchar(20),
TotalB nvarchar(20)
)
 
insert into DATA
select null,'10' union all
select '20','10' union all
select '18',null union all
select null,null
 
 SELECT CAST(Count(isnull(TotalA,0)) AS INT)/CAST(Count(isnull(TotalB,0)) AS INT)*100 AS GrandTotal FROM DATA
 

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

You need to combine the suggestions.

The ISNULL() recommendation was trying to get rid of NULLs from the count which since you are counting over same rows with no conditional will result in same count in the numerator and denominator; hence, 1 is always result.  So ADD conditional (CASE) statement.

To make this work, a good approach is to use SUM() versus COUNT().

e.g., SUM( CASE WHEN TotalA IS NOT NULL THEN 1 ELSE 0 END )
SUM( CASE WHEN TotalA IS NOT NULL THEN 1 END ) also works...

The conversion / casting to decimal removes the issue of integer division in SQL.  However, you can overcome this by multiplying by 1.0 also.  Whenever I am dealing with percentages or anything that I multiply by 100 later I usally take advantage of that and just do that in the numerator.

Here is what I mean all together :

SELECT AName
    , SUM( CASE WHEN TotalA IS NOT NULL THEN 1 END ) * 100.0 / SUM( CASE WHEN TotalB IS NOT NULL THEN 1 END ) AS GrandTotal
FROM DATA
GROUP BY AName
;

And taking into consideration division by zero, you can do this :

SELECT AName
    ,  CASE B WHEN 0 THEN 0 ELSE A * 100.0 / B END AS GrandTotal
FROM (
   SELECT AName
        , SUM( CASE WHEN TotalA IS NOT NULL THEN 1 END ) AS A
        , SUM( CASE WHEN TotalB IS NOT NULL THEN 1 END ) AS B
   FROM DATA
   GROUP BY AName
)  derived
;

Hope that helps.

And actually, the aggregates should be filtering NULLs, so you probably have empty strings instead of NULLs.

SELECT AName
    ,  CASE B WHEN 0 THEN 0 ELSE A * 100.0 / B END AS GrandTotal
FROM (
   SELECT AName
        , SUM( CASE WHEN NULLIF(TotalA, '') IS NOT NULL THEN 1 END ) AS A
        , SUM( CASE WHEN NULLIF(TotalB, '') IS NOT NULL THEN 1 END ) AS B
   FROM DATA
   GROUP BY AName
)  derived
;

Above uses NULLIF to force '' to NULL so we can evaluate NULLs and '' the same.

Sorry it must be late, one last one.  As others have posted, it should work with very simple COUNT() so this should also work :

SELECT AName
    ,  CASE B WHEN 0 THEN 0 ELSE A * 100.0 / B END AS GrandTotal
FROM (
   SELECT AName
        , COUNT( NULLIF(TotalA, '') ) AS A
        , COUNT( NULLIF(TotalB, '') ) AS B
   FROM DATA
   GROUP BY AName
)  derived
;

Here is my Database Structure. The GrandTotal Column is still not calculating properly. I still do not understand why if I use +, -, or * that it calculates the column

AName nvarchar(50)
TotalB nvarchar(50)
TotalA nvarchar(50)

AName        TotalA            TotalB
DAN             20      12
JOHN            20      10
DAN             18      8
DAN             50      30
JOHN            100      75

have you tried this query

SELECT AName, (sum(CAST(TotalA AS INT))/sum(CAST(TotalB AS INT)))*100 AS GrandTotal FROM DATA
group by AName


or tell me your desired output.

Are you trying to actually use the values as numbers?

When you use +, -, or * an implicit cast has to occur and would work if the data is always numeric.

Are you actually looking for this result :

208 * 100.0 / 135 = 154.07

I suspect you may have some values in TotalA and/or TotalB that are not able to be cast to numeric (i.e. Apha characters). Try this:

SELECT AName, SUM(CAST(CASE WHEN ISNUMERIC(TotalA) THEN TotalA ELSE 0 END AS DECIMAL(10,2))) /
             SUM(CAST(CASE WHEN ISNUMERIC(TotalB) THEN TotalB ELSE 0 END AS DECIMAL(10,2))) * 100 AS GrandTotal
FROM DATA

This is an example of my desired output

AName      TotalA      TotalB      GrandTotal
Dan      20      12      60
John      20      14      70
Dan      18      7      38.89
Dan      50      40      80
John      70      25      35.71

Column TotalA will have Yes Values and Column TotalB will have No Values. I trying to count them and get a score

Are you already showing us the grouped data by AName?

You indicate you want to count them and get a score, but the results appear to be percentage of B to A which is reverse of what showed earlier by the way.  Unless you have a type-o in the column headers above.

Select AName, TotalA, TotalB, TotalB * 100.0 / TotalA As Grandtotal
From DATA
;

Should give you the results above starting from data structure you gave.  Therefore, the COUNT() you were previously using leads me to believe the raw data is different, but maybe you simply need what I just posted.

And don't forget you have to convert the columns to numeric data type for the calculation to work, but in essence what I shown is what you are doing (i.e., dividing B by A and multiplying by 100 instead of the other way around).

mwvista1: I am not sure what you mean. My raw data isnt any different then what I originally posted. My last post was an example in response to MuhammadKashif. Maybe my example was not clear.

I have about 200 rows of yes values in TotalA and no values in TotalB. No null values in either column. I want to count them and divide TotalA by TotalB then multiply it by 100 to get a percentage score; which is the grand total column. Does my example make sense?

Definitely not clear and didn't see any original posting.  Just what you last did which showed integer values in the columns and the desired output of :

AName      TotalA      TotalB      GrandTotal
Dan      20      12      60

Makes me think you need 12 / 20 which is 3 / 5 OR 60%.

"I have about 200 rows of yes values in TotalA and no values in TotalB. No null values in either column. I want to count them and divide TotalA by TotalB then multiply it by 100 to get a percentage score; which is the grand total column."

If you have 200 rows of each value with NO null values; then, when you count each column, you will and should get 200.  Subsequently, 200 / 200 OR x / x will always be 1.

So again, there must be something else you need to do the calculation appropriately, so it would be best if you actually showed a sample of the raw data and we can construct the query that may help.

Regards,

Kevin

Hello:

OK. I figured out part of the problem, the data was imported into the database wrong. It makes sense that every calculation would be 1. Anyway, I am still having problems calculating the columns. With the new data Column TotalA will have a combination of yes no and maybe responses and column TotalB will have all yes responses. I still need to divide column TotalB by TotalA where TotalA = 'Yes' to get a total.
Sorry for the confusion I was concentrating more on than the sql statement than the actual data in the database.