SQL Server - Using Sum of Count

Question

I am using SQL Server. The Formula I need to use is (Good-Bad)/Total_Responses. A Good is when the value is >=9 and a Bad is <=6.

The Data I have is:

DATE        Q1  Q2
2012-03-04   9   9
2012-03-04   8   8
2012-03-04   7   9
2012-03-04   4  NA
2012-03-04  10  10
2012-03-04   8   3
2012-03-04   3   4
2012-03-04   2   6
2012-03-04   6   8
2012-03-04  NA   6

I know I am going to have to use a "CASE WHEN ISNUMERIC(Q1)=1" to make sure it does not use the NA values (They are stored as Nvarchar)

So the formula would end up being (5-8)/18=-0.16666 So overall I am trying to get the data to look like:

DATE        Promotor_Score
2012-03-04        -0.16666

Thank You!

Why NVARCHAR? Do you expect to store umlauts, pound symbols and Hebrew? — Aaron Bertrand, Apr 30 '12 at 15:21
Be careful of `ISNUMERIC`... it does not always do what you think it should. — Jeremy Holovacs, Apr 30 '12 at 15:23
To illustrate the point made by @JeremyHolovacs - I wrote this post in 2002, still relevant today: http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html — Aaron Bertrand, Apr 30 '12 at 15:23
@AaronBertrand Do you know off hand if `(IsNumeric(Q1 + '.0e0') = 1)` catches all the same values IsReallyNumeric catches? I've been using that [since I read the SO question Cast And IsNumeric](http://stackoverflow.com/questions/338075/cast-and-isnumeric) — Conrad Frix, Apr 30 '12 at 15:30
@Conrad All valid INTs, I would think so, but not 100% sure as I haven't tested. — Aaron Bertrand, Apr 30 '12 at 15:32

David M · Accepted Answer · 2012-04-30T15:30:45.300

4

Does this do the trick?

SELECT  Date,
        CAST((SUM(CASE WHEN ISNUMERIC(Q1) != 1 THEN 0
                 WHEN CAST(Q1 AS int) >= 9 THEN 1
                 WHEN CAST(Q1 AS int) <= 6 THEN -1
                 ELSE 0 END)
        + SUM(CASE WHEN ISNUMERIC(Q2) != 1 THEN 0
                 WHEN CAST(Q2 AS int) >= 9 THEN 1
                 WHEN CAST(Q2 AS int) <= 6 THEN -1
                 ELSE 0 END)) AS float)
        / (SUM(CASE WHEN ISNUMERIC(Q1) != 1 THEN 0
                   ELSE 1 END)
        + SUM(CASE WHEN ISNUMERIC(Q2) != 1 THEN 0
                   ELSE 1 END))
FROM    Questions
GROUP BY Date

Or if 'NA' is the only non-numeric value, test for it explicitly.

edited Apr 30 '12 at 15:30

answered Apr 30 '12 at 15:24

David M

71,481
13
158
186

I get this result: `2012-03-04 | 0` - you need to be careful about integer math. Did you try your code? – Aaron Bertrand Apr 30 '12 at 15:26
@Aaron - it's doing integer division, as you say. Fixed. – David M Apr 30 '12 at 15:28
Look a bit closer. It's there. Line 9. Only numerator or denominator needs casting for force floating point division, as I'm sure you know. – David M Apr 30 '12 at 15:29
Getting incorrect syntax near ). Highly recommend you get your code working before posting 15 updates. DOUBLE is not a valid data type in SQL Server. – Aaron Bertrand Apr 30 '12 at 15:30
@Aaron - where does 15 come from? – David M Apr 30 '12 at 15:35
I was exaggerating a little. I meant that you changed the answer multiple times from one non-working version to another. – Aaron Bertrand Apr 30 '12 at 15:46
1

I prefer to think of it as iterating towards a working solution. ;) Fair comment. – David M Apr 30 '12 at 15:47
HAH it would make sense that SO supports Agile! – Jeremy Holovacs Apr 30 '12 at 18:09

Gerardo Lima · Answer 2 · 2012-04-30T16:10:42.730

0

Buildin on @DavidM answer, I added float conversion and Q2 column awareness:

SELECT [date], 
1e0 -- a float multiplier to avoid integer value
* SUM( 0
    -- get the positive, negative or neutral from q1
    + CASE WHEN ISNUMERIC(q1) != 1 THEN  0
    WHEN CAST(q1 AS int) >= 9    THEN  1
    WHEN CAST(q1 AS int) <= 6    THEN -1
    ELSE 0 END
    -- get the positive, negative or neutral from q2
    + CASE WHEN ISNUMERIC(q2) != 1 THEN  0
    WHEN CAST(q2 AS int) >= 9    THEN  1
    WHEN CAST(q2 AS int) <= 6    THEN -1
    ELSE 0 END
)
/ SUM( 0
    -- get the number of valid questions from q1
    + CASE WHEN ISNUMERIC(q1) != 1 THEN 0 ELSE 1 END        
    -- get the number of valid questions from q2
    + CASE WHEN ISNUMERIC(q2) != 1 THEN 0 ELSE 1 END
)
FROM Questions
GROUP BY Date;

edited Apr 30 '12 at 16:10

answered Apr 30 '12 at 15:34

Gerardo Lima

6,467
3
31
47

Two quick comments (1) shouldn't your last ISNUMERIC check be against Q2? I think it currently only works because both columns have the same number of rows that either pass or fail the check (2) I think relying on ISNUMERIC is dangerous since any old garbage can be entered into that column. If the OP confirms that `'NA'` is the only possible non-numeric value, it is safer to check for that value explicitly than to rely on ISNUMERIC. – Aaron Bertrand Apr 30 '12 at 15:50
Sure @AaronBertrand: (1) was a mistype and it is corrected; (2) if only 'NA' values should be not considered, what to do with other (garbage) values? I don't recommend saving numeric data on `VARCHAR` at all, but that was part of the problem @Brad presented. – Gerardo Lima Apr 30 '12 at 16:13

SQL Server - Using Sum of Count

2 Answers2