question

postonoh avatar image
postonoh asked

sum question

I know this is a real basic question but I am drawing a blank. select top (1000) n.Project_ID, n.BidPackage_ID,n.Date, SUM(n.MAF + n.MAM + n.MIF + n.MIM) as 'Total # Of Employees', SUM(n.MIM) as 'Minority Employees', SUM(n.MIF) as 'Female Employees', Case when p.Cleveland_Resident = 1 then count(*) else 0 end as 'Cleveland Residents' from Narrative n, PersonnelSummary p where n.Date between '2011-01-01' and '2011-01-31' and n.Date = p.Date and n.Project_ID = p.Project_ID and n.BidPackage_ID = p.BidPackage_ID group by n.Project_ID, n.BidPackage_ID, n.Date, p.Cleveland_Resident order by n.Project_ID, n.BidPackage_ID, n.Date asc my readout is this. Project_ID BidPackage_ID Date Total # Of Employees Percentage Minority Employees Female Employees Cleveland Residents 96 98 2011-01-03 0 0.000000 0 0 0 96 98 2011-01-03 0 0.000000 0 0 9 96 109 2011-01-03 75 0.750000 25 0 0 I trying to get the total # of employee next column Percentage to show as 100 percent. so Total # Of Employees / Total of Employees = 1 or 100 percent
sql-server-2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
something like: ..., convert(int,100.0*convert(decimal(10,2), Total#Employees)/convert(decimal(10,2), TotalEmployees)), ... edit - converted to decimal(10,2) rather than floats at @Oleg's suggestion... ...and I prescribe an early night for me, as, apparently, my brain stopped working a few hours ago.
9 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@ThomasRushton Not to float though :( I mean that float should be absolutely prohibited from ever being used in SQL Server unless some obscure scenario forces one to do it. (distance between stars or something when it is OK to tolerate discrepancies in calculations)
1 Like 1 ·
Oleg avatar image Oleg commented ·
@ThomasRushton Unfortunately for me I learned the hard way that the good ol' friendly float of the front end languages like C# has nothing to do with the almost useless evil float of SQL Server. So, I fell into the trap one day just because both have the same name. Converting anything to double is evil in C#, but this is a different story. I stopped using that insanity the first time I had to debug a code falling into infinite loop because of the comparison like **someDoubleVariable = 0** which is not really true every time though it is true most times but not always :)
1 Like 1 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
@Oleg - fair enough. Convert(double...) do you? ;-)
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@ThomasRushton There is no way I could suggest conversion to **double**, because it does not exist unless I don't understand something in the answer. I meant to say that the original **convert(float,Total#Employees)** should be restated to something like **convert(decimal(10,2),Total#Employees)**. This is simply because the float in SQL Server has a different meaning and should not be used casually, that is all. I never heard of the **double** data type in SQL Server, please correct me and accept my apologies if I am wrong.
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
@Oleg - No, it's my brain failing. Please forgive me for putting words into your mouth.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.