Solved

Help with a SUM calculation -- data type conversion error

Posted on 2012-03-23
15
348 Views
Last Modified: 2012-03-27
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
Comment
Question by:britpopfan74
  • 8
  • 6
15 Comments
 
LVL 10

Expert Comment

by:plummet
Comment Utility
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

by:UnifiedIS
Comment Utility
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

by:plummet
Comment Utility
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
 

Author Comment

by:britpopfan74
Comment Utility
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

by:plummet
Comment Utility
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

by:britpopfan74
Comment Utility
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

by:plummet
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:britpopfan74
Comment Utility
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
 
LVL 10

Accepted Solution

by:
plummet earned 500 total points
Comment Utility
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
 

Author Comment

by:britpopfan74
Comment Utility
Perfection!! Just needed to add a comma after "SumCOMP" and it worked beautifully.

Thank you so much for your help!!
0
 

Author Comment

by:britpopfan74
Comment Utility
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

by:plummet
Comment Utility
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

by:plummet
Comment Utility
Ah, you're way ahead of me.

Thanks
0
 

Author Comment

by:britpopfan74
Comment Utility
Hope the points are allocated; was a HUGE help!!
0
 
LVL 10

Expert Comment

by:plummet
Comment Utility
Yes, thanks it was all sorted out yesterday. I'm very glad to have helped!

John
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now