Working Out a Percentage

Question

0.00/5 (No votes)

See more:

I have posted some script below which works out the totals

SQL

SELECT *
FROM (
SELECT cast([Id] AS VARCHAR(20)) AS ID
,[Site]
,convert(VARCHAR(20), [jobdate], 101) AS [job date]
,[submittime]
,[totalfirstjobs]
,[totalSecondJobs]
,[totalThirdjobs]
,[nightwork]
,[totalfirstjobs] + [totalsecondjobs] + [totalthirdjobs] + [nightwork] AS [Total all Jobs]
FROM site_total
WHERE convert(VARCHAR(20), [jobdate], 101) = convert(VARCHAR(20), getdate(), 101)

UNION

SELECT 'Totals'
,NULL
,convert(VARCHAR(20), [jobdate], 101)
,NULL
,sum(totalfirstjobs)
,sum(totalsecondjobs)
,sum(totalthirdjobs)
,sum(nightwork)
,sum(totalfirstjobs) + sum(totalsecondjobs) + sum(totalthirdjobs) + sum(nightwork)
FROM site_total
GROUP BY [jobdate]
) tbla

where cast([job Date] as date) =cast(getdate() as date)
ORDER BY [job Date ]

What i need to do is get a percentage for the first jobs, this needs to be an extra colum in the table below is how it needs to be worked out

I have made an error its got to be
total overall 1st Jobs divided by total overall jobs times by 100 = %

I just need some way of getting the percentage using the above method not working with percentage so hence why asking for help on implementing this.

Posted 22-Aug-14 1:42am

Member 11026392

Updated 22-Aug-14 4:47am

v4

Add a Solution

Comments

OriginalGriff 22-Aug-14 7:54am

And?
What have you tried?
Where are you stuck?
What help do you need?

Member 11026392 22-Aug-14 8:24am

I am stuck on how to implement the percentage side of things

Member 11026392 22-Aug-14 8:27am

I have never done percentages before so struggling

PhilLenoir 22-Aug-14 9:45am

It's still npt clear to me what you exactly want want: Percentages on each side of the union? (so percentage of "firstjobs" against jobs for that "jobdate" and percentage by all sites) or percentage of jobs at all sites (harder). What are the data types of your "jobs" columns?

Member 11026392 22-Aug-14 9:53am

What i need is the percentage of the total number of first job for all the sites using the below format.
Take the total number of jobs divide by 100 the multiply by the number of first jobs this will give you the first job percentage.

This is how the sql is setup
[Id] INT IDENTITY (1, 1) NOT NULL,
[site] VARCHAR (50) NULL,
[jobdate] DATETIME NULL,
[submittime] VARCHAR (50) NULL,
[totalFirstJobs] INT NULL,
[totalSecondJobs] INT NULL,
[totalThirdJobs] INT NULL,
[nightwork] INT NULL,

PhilLenoir 22-Aug-14 10:01am

Sorry if I seem dense, but you say "for all sites", do you mean "for each site". Let's give an example: lets say there are three sites and on a given date each site has one of each type of job. Do you wnat an answer of 33% or 11%?

Member 11026392 22-Aug-14 10:32am

I need the percentage to be all the sites combined for the overall total and the overall first job total

PhilLenoir 22-Aug-14 10:47am

I'm sorry, the language you are using is not precise enough. If you can indicate 11 or 33, give a concrete example of your own or show your attempts I can't give you an exact answer.

Member 11026392 22-Aug-14 10:46am

I think i have done an error, its got to be total overall 1st Jobs divided by total overall jobs times by 100 = %

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PhilLenoir · Accepted Answer · 2014-08-22T04:59:00

Solution 1

I think I understand you now. The big issues here are that the data types are ints and nullable, so if there are nulls, the results returned will be null and without using casts (to float) the expression is not commutative.

I'll assume that you don't need to any decimal places (if you do, cast all expressions to floats) and the sums are never null (if they are, use ISNULL function):

SELECT *
FROM (
SELECT cast([Id] AS VARCHAR(20)) AS ID
,[Site]
,convert(VARCHAR(20), [jobdate], 101) AS [job date]
,[submittime]
,[totalfirstjobs]
,[totalSecondJobs]
,[totalThirdjobs]
,[nightwork]
,[totalfirstjobs] + [totalsecondjobs] + [totalthirdjobs] + [nightwork] AS [Total all Jobs]
,(ISNULL([totalfirstjobs],0)*100)/(ISNULL([totalfirstjobs],0) + ISNULL([totalsecondjobs],0) + ISNULL([totalthirdjobs],0) + [nightwork]) AS percentfirstjobs
FROM site_total
WHERE convert(VARCHAR(20), [jobdate], 101) = convert(VARCHAR(20), getdate(), 101)

UNION

SELECT 'Totals'
,NULL
,convert(VARCHAR(20), [jobdate], 101)
,NULL
,sum(totalfirstjobs)
,sum(totalsecondjobs)
,sum(totalthirdjobs)
,sum(nightwork)
,sum(totalfirstjobs) + sum(totalsecondjobs) + sum(totalthirdjobs) + sum(nightwork)
,(sum([totalfirstjobs])*100)/(sum([totalfirstjobs]) + sum([totalsecondjobs]) + sum([totalthirdjobs]) + [nightwork]) AS percentfirstjobs
FROM site_total
GROUP BY [jobdate]
) tbla

where cast([job Date] as date) =cast(getdate() as date)
ORDER BY [job Date ]

Please note that I've not dealt with a divide by zero problem if that's a possibility.

Posted 22-Aug-14 4:59am

PhilLenoir

Comments

Member 11026392 22-Aug-14 13:07pm

I am getting this error

Msg 8120, Level 16, State 1, Line 27
Column 'site_total.nightwork' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8155, Level 16, State 2, Line 32
No column name was specified for column 1 of 'tbla'.
Msg 8155, Level 16, State 2, Line 32
No column name was specified for column 2 of 'tbla'.
Msg 8155, Level 16, State 2, Line 32
No column name was specified for column 3 of 'tbla'.
Msg 8155, Level 16, State 2, Line 32
No column name was specified for column 4 of 'tbla'.
Msg 8155, Level 16, State 2, Line 32
No column name was specified for column 5 of 'tbla'.
Msg 8155, Level 16, State 2, Line 32
No column name was specified for column 6 of 'tbla'.
Msg 8155, Level 16, State 2, Line 32
No column name was specified for column 7 of 'tbla'.
Msg 8155, Level 16, State 2, Line 32
No column name was specified for column 8 of 'tbla'.
Msg 8155, Level 16, State 2, Line 32
No column name was specified for column 9 of 'tbla'.
Msg 207, Level 16, State 1, Line 32
Invalid column name 'job Date'.
Msg 207, Level 16, State 1, Line 33
Invalid column name 'job Date '.

Member 11026392 22-Aug-14 13:27pm

Changed the code to the following and it works
SELECT *
FROM (
SELECT cast([Id] AS VARCHAR(20)) AS ID
,[Site]
,convert(VARCHAR(20), [jobdate], 101) AS [job date]
,[submittime]
,[totalfirstjobs]
,[totalSecondJobs]
,[totalThirdjobs]
,[nightwork]
,[totalfirstjobs] + [totalsecondjobs] + [totalthirdjobs] + [nightwork] AS [Total all Jobs]
,(ISNULL([totalfirstjobs],0)*100)/(ISNULL([totalfirstjobs],0) + ISNULL([totalsecondjobs],0) + ISNULL([totalthirdjobs],0) + ISNULL([nightwork],0)) AS percentfirstjobs
FROM site_total
WHERE convert(VARCHAR(20), [jobdate], 101) = convert(VARCHAR(20), getdate(), 101)

UNION

SELECT 'Totals'
,NULL
,convert(VARCHAR(20), [jobdate], 101)
,NULL
,sum(totalfirstjobs)
,sum(totalsecondjobs)
,sum(totalthirdjobs)
,sum(nightwork)
,sum(totalfirstjobs) + sum(totalsecondjobs) + sum(totalthirdjobs) + sum(nightwork)
,(sum([totalfirstjobs])*100)/(sum([totalfirstjobs]) + sum([totalsecondjobs]) + sum([totalthirdjobs]) + sum([nightwork])) AS percentfirstjobs
FROM site_total
GROUP BY [jobdate]
) tbla

where cast([job Date] as date) =cast(getdate() as date)
ORDER BY [job Date ]

PhilLenoir 25-Aug-14 9:21am

Great, I had assumed that your original query was OK without the percentages. The important thing to understand here is that the math is not commutative because SQL Server casrs as it goes: Let's suppose we have 10 out of 100 = 10%. If we divide 10 by 100, cast to an integer the answer is zero. If we then multiply by 100 we get zero. However, if we multiply 10 by 100 we get 1000. Finally dividing by 100, we get 10 (percent) = the answer we're after!

Working Out a Percentage

1 solution

Solution 1

Add your solution here

Preview 0

Working Out a Percentage

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us