Solved

Help with a SUM calculation -- data type conversion error

Posted on 2012-03-23
15
349 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
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

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

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

Open in new window


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

Cheers
John
0
 

Author Comment

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

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

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

by:plummet
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:britpopfan74
ID: 37766176
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
ID: 37766371
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
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

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

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

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

Thanks
0
 

Author Comment

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

Expert Comment

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

John
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

895 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

17 Experts available now in Live!

Get 1:1 Help Now