Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

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
0
britpopfan74
Asked:
britpopfan74
  • 8
  • 6
1 Solution
 
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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
 
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now