Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Help with a SUM calculation -- data type conversion error

Posted on 2012-03-23
Medium Priority
363 Views
I am writing a query in SQL to summarize the total number of members that are compliant ("yes" are screened) in each region.

My problem is everytime I try to calculate the SUM, I get an "error stack overflow" message or datatype conversion error (e.g. Error converting data type varchar to float) -- have tried float and bigint, which seemed to make sense but I feel like I'm missing something simple...

Any help is much appreciated!

SELECT * FROM #COMP_YES
SELECT REGION, COUNT(*) AS TOTAL_COMPLIANT
--SUM(cast(REGION as FLOAT)) as TotalExp
--Sum(CAST(REGION AS BIGINT) / 100) AS GRAND_TOT
INTO #COMP_YES
FROM #NON_SCNS
GROUP BY REGION
ORDER BY COUNT(*) DESC
0
Question by:britpopfan74
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 8
• 6

LVL 10

Expert Comment

ID: 37759399
Hi,

I wonder why you're trying to sum the region, if the region is a code? I'd have thought that the count(*) would show the number of members? Have I got the wrong end of the stick?
0

LVL 18

Expert Comment

ID: 37759929
Yeah, definite type mismatch.  varchars are usually used for alphanumeric values.  Even if the regions were numeric values stored as varchar, say 1298, 1500 ,90
Sum adds up the values but in that case it is a meaningless number.
0

LVL 10

Expert Comment

ID: 37760206
If you can post some examples of data we will find it easier, I'm guessing your region field is varchar containing a number, but I don't think you want to use the sum of that number as that would seem pointless - you want a count of all compliant members by region in which case this would seem to be the sort of thing, pretty much what you already have:

``````SELECT REGION, COUNT(*) AS TOTAL_COMPLIANT
FROM #NON_SCNS
group by REGION
order by count(*) desc
``````

..unless, as I said before, I am misunderstanding the requirement.

Cheers
John
0

Author Comment

ID: 37765745
Certainly...sorry if this was unclear -- will try explain better

...what I have in the analysis is I go through and present for the client the detail (contact info, max date of screening) for the compliant members then contact info for non-compliant members.

After presenting the detail, I want to present the summary by region of compliant and non-compliant members.

SELECT REGION, COUNT(*) AS TOTAL_COMPLIANT
FROM #SCNS
GROUP BY REGION
ORDER BY COUNT(*) DESC

Currently, there are 16 regions with counts (not showing full region name)

REGION      TOTAL_COMPLIANT
1A      1446
7B      400
5C      246
5D      208
1E      177
1F      112
1G      68
5H      58
5I      55
5J      35
1K      31
5L      21
7M      19
5N      9
1O      3
1P      1

I could export and do the summary stats in Excel but was hoping to have it all "automated" into the SQL, just stuck on the datatype conversion for the SUM.
0

LVL 10

Expert Comment

ID: 37765884
Ummm so, is what you want to see

REGION                TOTAL_COMPLIANT             TOTAL_NON_COMPLIANT
1A                                              1446                                            1234
7B                                                400                                            5678

etc?

I'm still not sure!
0

Author Comment

ID: 37766020
Sorry -- Didn't think it all the way through!

YesCOMP      NoCOMP      SUM      YES%      NO%
1446      1375      2821      51%      49%
400      349      749      53%      47%
246      159      405      61%      39%
208      253      461      45%      55%
177      174      351      50%      50%
112      64      176      64%      36%
68      115      183      37%      63%
58      56      114      51%      49%
55      71      126      44%      56%
35      20      55      64%      36%
31      38      69      45%      55%
21      30      51      41%      59%
19      21      40      48%      53%
9      4      13      69%      31%
3      2      5      60%      40%
1      2      3      33%      67%

If I want to get the "YES" and "NO" compliants together, did the below to get them matched up by region then did the rest in Excel to get the sums and percentages from the screened vs. non-screening groups

--DROP TABLE #COMP_YES
--SELECT * FROM #COMP_YES
SELECT REGION, COUNT(*) AS YesCOMP
INTO #COMP_YES
FROM #NON_SCNS
GROUP BY REGION
ORDER BY COUNT(*) DESC

--DROP TABLE #COMP_NO
--SELECT * FROM #COMP_NO
SELECT REGION, COUNT(*) AS NoCOMP
INTO #COMP_No
FROM #temp_FINAL_SCN
GROUP BY REGION
ORDER BY COUNT(*) DESC

--DROP TABLE #temp_COMP_TOT
--SELECT * FROM #temp_COMP_TOT
SELECT Y.REGION, Y.YesCOMP, N.NoCOMP
INTO #temp_COMP_TOT
FROM #COMP_YES Y
FULL OUTER JOIN #COMP_No N ON Y.REGION = N.REGION
0

LVL 10

Expert Comment

ID: 37766143
Hi again,

Do you have one table with the original data, these are all temp tables. For example, a table that has fields REGION, COMPLIANT, etc? Because it may be possible to pull data from these temp tables together but I think it should all be done in one hit. I think we're nearly there, though!
0

Author Comment

ID: 37766176
Thanks -- let me send you my original code, just take a second to take out any identifying info

0

LVL 10

Accepted Solution

plummet earned 2000 total points
ID: 37766371

``````SELECT
Y_COMP.REGION,
Y_COMP.YesCOMP,
N_COMP.NoCOMP,
(Y_COMP.YesCOMP + N_COMP.NoCOMP) as SumCOMP
cast((N_COMP.NoCOMP * 100 / (N_COMP.NoCOMP+Y_COMP.YesCOMP)*100)/100 as varchar) + '%' as No_PC,
cast((Y_COMP.YesCOMP * 100 / (N_COMP.NoCOMP+Y_COMP.YesCOMP)*100)/100 as varchar) + '%'as Yes_PC
from
(SELECT
REGION,
COUNT(*) AS YesCOMP
FROM #NON_SCNS
GROUP BY REGION) Y_COMP
inner join
(SELECT
REGION,
COUNT(*) AS NoCOMP
FROM #temp_FINAL_SCN
GROUP BY REGION) N_COMP
on N_COMP.REGION = Y_COMP.REGION
``````

Obv I don;t have your data but I think it should work. Fingers crossed
0

Author Comment

ID: 37766801
Perfection!! Just needed to add a comma after "SumCOMP" and it worked beautifully.

Thank you so much for your help!!
0

Author Comment

ID: 37766823
I would like to accept plummet's correct response for 500 pts, not "britpopfan74 requested that this question be closed by accepting britpopfan74's comment #37766801 (0 points)"
0

LVL 10

Expert Comment

ID: 37766827
You're welcome, but you're not allocating any points to me - which is fine if my answers weren't useful... but hopefully they were :-o

Cheers
John
0

LVL 10

Expert Comment

ID: 37766830
Ah, you're way ahead of me.

Thanks
0

Author Comment

ID: 37772314
Hope the points are allocated; was a HUGE help!!
0

LVL 10

Expert Comment

ID: 37772449
Yes, thanks it was all sorted out yesterday. I'm very glad to have helped!

John
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includâ€¦
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
###### Suggested Courses
Course of the Month7 days, 4 hours left to enroll