Help with a SUM calculation -- data type conversion error

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
britpopfan74Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plummetCommented:
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
UnifiedISCommented:
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
plummetCommented:
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

Open in new window


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

Cheers
John
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

britpopfan74Author Commented:
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
plummetCommented:
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
britpopfan74Author Commented:
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
plummetCommented:
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
britpopfan74Author Commented:
Thanks -- let me send you my original code, just take a second to take out any identifying info

uploaded sql script
qryUpload-BCS.sql
0
plummetCommented:
Blimey, that's a bit complicated..to go back then to your temp tables ;-) how about this?

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

Open in new window


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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
britpopfan74Author Commented:
Perfection!! Just needed to add a comma after "SumCOMP" and it worked beautifully.

Thank you so much for your help!!
0
britpopfan74Author Commented:
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
plummetCommented:
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
plummetCommented:
Ah, you're way ahead of me.

Thanks
0
britpopfan74Author Commented:
Hope the points are allocated; was a HUGE help!!
0
plummetCommented:
Yes, thanks it was all sorted out yesterday. I'm very glad to have helped!

John
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.