Calculating Percentages For Each Row

Dec 10, 2007

One more for you while I'm trying to remember my SQL skill from a few years ago (last one of the day - promise):
My database looks like:

(Ignore that the ERD is in Access, the database is SQL Server 2005)

How do I calculate sales percentages?
I.E. how do I calculate the percentage that each product has sold as a percentage of the total sales?

What I'm aiming for is each row of products is shown, with its percentage of the whole sales.

AVG is involved this time, isn't it?

The productID is of the int datatype (not that you need to know that field - but never mind) and the quantity is a smallint, before you ask


Thank you in advance for any replies.


Paul

View 5 Replies


ADVERTISEMENT

Calculating Percentages And Showing In Separate Columns

Aug 31, 2013

SELECT DISTINCT CASE WHEN SM.SERVICE_TYPE_N = 1 THEN 'LABORATORY'
WHEN SM.SERVICE_TYPE_N = 2 THEN 'PODIATRY'
WHEN SM.SERVICE_TYPE_N = 3 THEN 'ADMINISTRATION'
WHEN SM.SERVICE_TYPE_N = 4 THEN 'DIET'
WHEN SM.SERVICE_TYPE_N = 5 THEN 'DENTAL'

[Code] ....

In the above query i need to calculate 100%,30% and percentage other than 100 and 30 and show them in separate columns how to do that?

1)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [100_PERCENT]
WHERE BM.BILL_AMOUNT_M=BM.CONCESSION_AMOUNT_M

2)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [30_PERCENT]
WHERE AND BM.CONCESSION_AMOUNT_M=BM.BILL_AMOUNT_M * 0.30

3) ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [OTHER_CONCESSION_PERCENT]
WHERE BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.001 and BM.BILL_AMOUNT_M*0.299
OR BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.301 and BM.BILL_AMOUNT_M*0.999

View 3 Replies View Related

Calculating Percentages Based On Multiple Rows

Oct 2, 2014

I have two tables that look like this (below). One tells me the name of my product, the Amazon Category it is in & the amount that I want to sell it for. The other tells me the Category & the fee for that category. So far so good. Though it gets tricky in the sense that some categories have two tiers. So in Electronics, the fee for $0.00 - $100.00 is 15%. But from $100 and up it is 8%.

Since it has two columns & both of the new columns pertain to the fee of my product, I can't figure out how to use both at once. For my $599.99 example it would be ($100 * 0.15) + ($499.99 * 0.08) = $55.00. Would I pivot the data? If not, how would I group it to be considered together?

Category Example

IDAmazonCategoryIDAmazonCategoryNameFeePercentageStartPriceEndPrice

1apsAllDepartments0.150.000.00
2instant-videoAmazonInstantVideo0.000.000.00
3appliancesAppliances0.150.000.00

Product Example

1Product1Electronics9.99
2Product3Electronics99.99
3Product2Electronics599.99

Raw SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #Amzn_Category_FeeStructure(
[ID] [int] IDENTITY(1,1) NOT NULL,

[Code] ....

I use Microsoft SQL 2008

View 2 Replies View Related

Using Percentages

May 1, 2008

The formula for percentage safe = sum(safe)/ sum(safe)+sum(unsafe)*100.0

This works well on a calculator. Could someone show me how to achieve this in sql please. If we use safe = 92 and unsafe = 117

This forum is great!

Regards Phill

View 5 Replies View Related

Comparing Percentages

Mar 17, 2008

hi

i have a three fields in my table..those are


FromPercentage -Varchar(30)
ToPercentage -Varchar(30)
Scale -Int(11)

i have the percenatge that 10%...now i want to check that whether this 10 % is in between the frompercentage and topercentage..if it is there i want to return the the scale value..how can i do this in sql.

i know comparing when it is integer using where and and operator..but i dont know how use if it has the percentages.


Ram

View 1 Replies View Related

Stuck On Percentages...

Apr 18, 2008

most my fields that I am interested in getting percentage values for have to be counted first.... I am having problems converting the counted values to percentages. I don't think I am doing it right...

I would greatly appreciate an example of a very simple matrix table with a counted value that displays percentages. I have found examples online but are part of a much more complex table and I can't seem to get thier formating to work on mine.

Kind regards,

Rich

View 6 Replies View Related

Using OVER Clause To Calculate Percentages

Jun 25, 2015

I've always user over with partitioning however recently I am looking at some other code and they have calculating percentages using over. I'm at a lost on why you would do this.

Example would be

CAST(TABLE1.UNIT AS DECIMAL (6,2)) / SUM(TABLE2.UNIT) OVER () AS UNIT_%

What about be the advantage of using OVER there?

View 3 Replies View Related

Calculation For Totals Using Percentages

Jan 25, 2006

I am trying to figure how write sql statement that will give the total cost.

unit price * quantity - discount = total cost

Unit price = 9.8
quantity = 30
discount = 0.15

View 2 Replies View Related

Return Percentages In The Query.

Oct 13, 2006

Hello,I'm trying to something that just works in Oracle, but does not in SqlServer.How can I get the percentage of two counts to be returned on each rowof the query?select count(sid), /* all not the not null */count(*),(count(sid) /count(*) ) as percent_not_null,4 as four,(3/4) as three_over_fourfrom dbo.sysusers7082040Incredibly, it changes even 3/4 into a zero!For efficiency, I want the percentage returned in the query.And to not use variables and coding. Efficiency,both of the server, and of my time.Note: I am using dbo.sysusers as an EXAMPLE only. My realquery will be on user defined application tables.What is the solution please?

View 4 Replies View Related

Expressing Values As Percentages

Jul 20, 2005

I have two values and I want to express a third derived value as apercentage of the other two values. I thought it would be a simpledivision of the first two numbers and then a multiplication by 100 togive me a percentage, but all I get is 0.Here is my select statement,SELECT dbo.Eligble.GRADETotal,dbo.nil1234_Faculties_Totals.FACTotal,dbo.nil1234_Faculties_Totals.FACTotal /dbo.Eligble.GRADETotal * 100 AS [PERCENT]FROM dbo.Eligble CROSS JOINdbo.nil1234_Faculties_TotalsCan anyone point out where I'm going wrong here?Thanks in advance

View 2 Replies View Related

How Do I Calculate Percentages? It's Supposed To Be EASY!

Jun 29, 2004

here's my code...

DECLARE @Output FLOAT
SELECT @Output=(@Part/@Whole)*100

When I substitute my parameters like this:

SELECT @Output=(5/20)*100

The answer is always 0.0!
But if I do this:

SELECT @Output=(5*100)/20

I get the correct answer.
Why is this so?
What is the correct way in SQL to calculate percentage??!!!

View 3 Replies View Related

Code To Calculate Percentages By Month?

Mar 30, 2015

I have a query which provides the total number of each of 2 invoice flags per month, starting 1st Feb this year. I also need a percentage calculated for each of the 2 flags by month. However, it is showing the totals correctly for each of the flags by month, but the percentage is using the total of both flags for all months, and so it is wrong if I only want to look at one month. The query is as follows along with a sample output including % which are incorrect.

How can I correct this so that it shows the right % for each of the flags for each month?

Select
sk.Period Period,
sk.[Invoice Flag],
count(*) * 100.0 / Sum(count(*)) Over () Percentage,
count(*) Total1
From
(Select wh.worknumber [Work Order],

[code].....

Output at present:

Period Invoice Flag Percentage Total1
--------------------------------- ------------------------------- --------------------------------------- -----------
2015-02 Invoiced after 7 days 5.704697986577 136
2015-03 Invoiced after 7 days 2.097315436241 50
2015-02 Invoiced with Customer Approval 59.563758389261 1420
2015-03 Invoiced with Customer Approval 32.634228187919 778

View 2 Replies View Related

Group Expression That Calculate Percentages.

Mar 8, 2007

I have an expression in a group that calculates percent of sales:

=iif(Sum(Fields!BOOKD.Value)=0,0,IIF(Sum(Fields!NET.Value)=0,0,((Sum(Fields!BOOKD.Value)-Sum(Fields!NET.Value))/Sum(Fields!BOOKD.Value))))*100

The problem I'm having is that if both the

Sum(Fields!BOOKD.Value)=0 and

Sum(Fields!NET.Value)=0 ,

the expression returns the message #ERROR instead of a 0.



I have the initial query set to display 0 if the value is null.

,SUM(ISNULL(R.BOOKD,0))BOOKD
,SUM(ISNULL(R.NET,0))NET



I would greatly appreciate any help on how to solve this issue.

Thank Barb

View 4 Replies View Related

Creating Dataset For Report - Percentages In Result

Nov 3, 2015

Trying to create a dataset for a report. I need to bring back percentage in the result set. The fields that I am using to get the percentage have valid data but the result field is 0.00%. Is there reason I cant bring back a percentage field?

Code:
SELECT JobNum, Mailed, LT_7, BT_6_10, GT_10,
Format([LT_7]/[Mailed], 'p') AS PctScaned1,
Format([BT_6_10]/[Mailed], 'p') AS PctScaned2,
Format([GT_10]/[Mailed], 'p') AS PctScaned3,

[Code] ....

View 3 Replies View Related

Label Stacked Bar/column Chart With Percentages

Mar 11, 2008

Hi,

I need to label stacked bar /column charts with the percentage of each series at each axis point.

For example, if I have the following values in the chart:

Series 1 Series 2
Axis point 1 50 25

Axis point 2 12 12



I need the labels to read: Series 1 Series 2
Axis point 1 67% 33%

Axis point 2 50% 50%


It would seem that I need to format the data point labels to read something like: sum (data.label)/sum(data.label, "axis point")

Any ideas would be appreciated. Thanks in advance for your elegant solutions

View 3 Replies View Related

Funky Formatting Of Percentages In Excel Export

Sep 18, 2007



I've created a report that has cells in it that I want to be formatted as percentages. I set the format code in the cell properties to "P1" and everything appears to work fine until I export to Excel. Some cells show up with only one digit after the decimal and some show up with two digits after the decimal.
I looked at the formatting in Excel (right click, Format Cells...) and it is set to "[$-1010409]#,##0.0#%". I don't have the first clue what that is "supposed" to do but what it does is this:

If I enter 100.00 in the cell it shows up as 100.0%. Expected
If I enter 100.10 in the cell it shows up as 100.1%. Expected
If I enter 100.15 in the cell it is displayed as 100.15%. Not expected

I have formatted the report to only show one digit after the decimal, but RS exports to Excel with some funky formatting that sometimes shows one digit and sometimes two digits after the decimal. Why wouldn't it simply format the cell as a percent? How can I get it to only show a single digit after the decimal?

This is Excel 2007 but Excel 2003 has the same behavior.

Thanks
--John

View 5 Replies View Related

Reporting Services 2005 - Matrix Grouped Report - Calculate Percentages

Jun 29, 2007

Hi,



Could someone help with the following problem, I what to create a cross-tab report of the following data, grouped by Colour:

RAW DATA








Name
Colour

Sarah
Black

Kim
Red

Jane
Black

Jane
Pink

Robert
Yellow

Tom
Green

Tom
Black

Billy
Black

Sarah
Black

Sarah
Black

Tom
Pink

Kim
Pink

Robert
Black



Group Colours by Names Report










Number





Proportions %





Name
Black
Green
Pink
Red
Yellow
Total by Name
Black
Green
Pink
Red
Yellow

Billy
1




1
100.00%
0.00%
0.00%
0.00%
0.00%

Jane
1

1


2
50.00%
0.00%
50.00%
0.00%
0.00%

Kim


1
1

2
0.00%
0.00%
50.00%
50.00%
0.00%

Robert
1



1
2
50.00%
0.00%
0.00%
0.00%
50.00%

Sarah
3




3
100.00%
0.00%
0.00%
0.00%
0.00%

Tom
1
1
1


3
33.33%
33.33%
33.33%
0.00%
0.00%





I want to produce a Matrix cross-tab report, like the above, within Reporting Services. Any suggestions welcome



Many Thanks,



Radha

View 5 Replies View Related

T-SQL And Calculating A Sum

Sep 26, 2006

I want to sum up a column, but only summing up the top 25 quantites and then I want to subtract the sum of all quantities from the total of the top 25...how would this be done with t-sql?

View 3 Replies View Related

Calculating PK

Jul 2, 2001

SQL7

I am interested in your opinion on the creation of primary keys in a table - simple ones that is. It would be the replacement for an Identity column.

Some say calculate the value off a lookup table, others say use an external object like c++ dll reading/incrementing from a flat file.

Using an identity column reduces portability of the table.

Thoughts ?

Craig

View 2 Replies View Related

Help !!!calculating Age

Nov 15, 2001

Hi,
What the best way to calculate the age given two datetimes.
I tried using the DATEDIFF(yyy,startdate,enddate) but that seems to only compare the year and not look at the specific day.

I am looking for something that would return the following output


birthday= 12/6/1973
EndDate = 11/30/2001
then the age should be 27

If birthday = 12/6/1973
enddate = 12/11/1973
then the age should be 28

thanks in advance
Zoey

View 3 Replies View Related

Calculating RFM

Apr 17, 2007



I'm in the process of developing a Kimball-method CRM data warehouse and I'd like to have Recency, Frequency, Monetary "scores" at the customer level. Am I better off creating these in the relational database and feeding them to SSAS or have SSAS do the aggregations and assign the "scores"?



Also, has anyone located any SSIS transform scripts that would calculated RFM scores? This is probably a pretty easy task in VB.net but I haven't gone down that road yet.



Steve

View 1 Replies View Related

Calculating The Sum Of Columns...

Oct 31, 2006

It may be simple question, but how can i get the sum of column? with conditions like getting the some of clicks one user send and so...

View 5 Replies View Related

Calculating Ages

Jul 3, 2007

When users register with my site they give me their DoB, how can I work out an age from it. I was thinkin about doing it from the year, but that isnt very accurate. Can anyone help? thanks si! ps Id only want to work out how many years old they are. si! 

View 7 Replies View Related

Calculating ID Numbers

Mar 28, 2008

 I needed to come up with an algorithm to create unique user- friendly account numbers such as AC0000000001, AC0000000002, etc...Where they increment by 1. I created a SQL function that retrieves the previous number generated, adds 1 to it, inserts the new value into  the table, then returns the new value. I started thinking, what if the function is ran at the same time? What if function # 1 creates the new number, and function #2 creates a new number as well before function #1 inserts it? Is this a possible scenario? If so, how do I lock the process until the function completes to prevent this?  Thanks for any help you provide.

View 2 Replies View Related

Calculating The 3rd Value By The First 2's Selection

May 30, 2008

Hi,
First off, let me state that i am new to ASP.net... and have had to hit the ground running... I have looked on the net but have not found a suitable explanation on how to do this.
Let me explain what i am trying to do: In my aspx page, i have 2 dropdown boxes where the user will select a skill catagory, and then a skill. Based on that selection, i would like to compute what the Service line will be. There is a primary and foreign key on CatID on the skills and SkillCat tables. This data would need to be inserted into the another table aswell, and this would need to go into a report later.
How do i go about doing this? Some examples would be greatly appreciated. And if you know of a good link, please post it for me.
 Skills Table









CatID
SkillID
Skills
SL_ID

1
4
Skill 1
3

2
5
Skill 2
2

2
6
Skill 3
2

3
7
Skill 4
4

3
8
Skill 5
4

4
9
Skill 6
NULL

4
10
Skill 7
NULL

5
11
Skill 8
3

6
12
Skill 9
3

7
13
Skill 10
4

8
14
Skill 11
1

9
15
Skill 12
3

9
16
Skill 13
3

10
17
Skill 14
6

11
18
Skill 15
1
Skills Catagory








CATID
Org
Skillcat

1
Org1
Skill Category 1

2
Org2
Skill Category 2

3
Org3
Skill Category 3

4
Org4
Skill Category 4

5
Org5
Skill Category 5

6
Org6
Skill Category 6

7
Org7
Skill Category 7

8
Org8
Skill Category 8

9
Org9
Skill Category 9
 Service Line Table







SL_ID
Service_line

1
Service Line 1

2
Service Line 2

3
Service Line 3

4
Service Line 4

5
Service Line 5

6
Service Line 6

7
Service Line 7
 
Thanks for all your help in advance.
Dash

View 1 Replies View Related

Calculating Avg Rowsize In My Db...

Nov 23, 2001

Hi,

I'm using SQL Server 7.0 SP3.

I need to calculate the average size of a record in my database to assist in estimating growth.

Is there a quick and/or easy way of doing this?

Thanks in advance,
Darrin

View 1 Replies View Related

Help With Calculating Dates.

Aug 26, 2005

Hi,

I'm fairly new to MS SQL, but I'm trying to query the time elapse between two dates. Example below...

Select
dbo.res.book_date,
dbo.res.arrive_date,
Cast (dbo.translog.systemdate as datetime)
From
dbo.res
Where
dbo.res.resid > '500'

The third line is where I need help with a statement. I need the elapse time. Ex. if the book date is 20050820100000 and the arrive date is 20050820120000, I would like the third Select statement to return 2:00 hours or something similar. Thanks.

View 11 Replies View Related

Calculating Time

Aug 27, 2005

Hello,
I am fairly new to this, but I need to write a query that calculates whether an order made a shipping cutoff based on creation time, and when it actually shipped, based on local customer time. All times in the table below are central time.
Heres where it gets complicated for me. If the order was created after 8:00am local customer time the same day, and shipped prior to 5pm local customer time, it would be considered a pass, or a 1. If an order was created after 8am local customer time, BUT didnt ship until AFTER 5pm local customer time, it would be considered a failure or a 0. And to complicate things even more, if the order is created after 5pm local customer time, the order create time would be treated as it was created at 8am local customer time the following day local customer time, and would need to be shipped prior to 5pm local customer time that day. Would CASE be appropriate for this? Or am I thinking wrong? Any help would really be appreciated!!


Ordernum OrderCreate OrderShipDate TimeZ
67890 5/5/2005 11:575/6/2005 15:33 EST
35789 5/5/2005 13:575/5/2005 14:19 MST
44465 5/5/2005 13:58 5/5/2005 21:58 CST
87654 5/5/2005 18:00 5/7/2005 21:58 PST

View 13 Replies View Related

Calculating Median In SQL

Aug 25, 2004

Hi All,
I have a table that of server names and their execution times that run in to hundreds of thousands of records. What i need is some SQL that gives me the median execution times for each of these different servers. At the moment i have some SQL that only gives me the median for all the records in the table not the median execution time for every different server name. For example my tables looks something like this;

ServerName | ExecTime
-----------------------
server1 | 0.07
server2 | 0.17
server1 | 0.27
server1 | 0.37
server2 | 0.47
server1 | 0.57
server1 | 0.67
server2 | 0.77

My SQL below gives me

ServerName | ExecTime
-----------------------
server1 | 0.37

Where as i want

ServerName | ExecTime
-----------------------
server1 | 0.37
server2 | 0.47

Here is my SQL, hope someone can modify it and thanks in advance.


Code:


SELECT DISTINCT instance, exec_time AS median
FROM (SELECT instance, exec_time
FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance
FROM (SELECT TOP 50 PERCENT exec_time, instance
FROM llserverlogs
ORDER BY exec_time) sub_a
ORDER BY 1 DESC) sub_1
UNION ALL
SELECT instance, exec_time
FROM (SELECT TOP 1 exec_time = exec_time * 1.0, instance
FROM (SELECT TOP 50 PERCENT exec_time, instance
FROM llserverlogs
ORDER BY exec_time DESC) sub_b
ORDER BY 1) sub_2)

View 4 Replies View Related

Calculating Dates

May 16, 2004

I have a table in my sql server that calculates renewal dates for me, that date is based on the final suit date. The table has the ssn, and the FinalSuitDate its in a one to many relationship with the employeetable.

the Finalsuit table is suppose to calculate the renewal dates(which I'm trying to do in a query) my original expression in access was using dateserial FirstRenewal: DateSerial(Year([FINALSUITDONE])+2,Month([FINALSUITDONE])+1,Day([FINALSUITDONE])=30), but sql does not recognize that.

For Example

If the Finalsuit is 12/01/2000
then the renewal would be 12/31/2002
the renewal are to be done on the last day of the month and two years from the finalsuit date. Problems is I'm having problems doing this in sql servers query?

Can someone out there help please

View 8 Replies View Related

Calculating Age In Years

Dec 19, 2001

--Use the following formula to calculate a person's age in years in a stored procedure,
--where @dob is the person's date of birth and @yyyymmdd is the date on which to determine the age:

DECLARE @age int
DECLARE @dob datetime
DECLARE @yyyymmdd varchar(11)

SELECT @dob = '12/06/1966'
SELECT @yyyymmdd = GETDATE()
SELECT @age = FLOOR(DATEDIFF(day, @dob, @yyyymmdd) / 365.25)
PRINT CONVERT(varchar, @age)

--Notes:
--Substitute "getdate()" for the @yyyymmdd variable if you want to determine the person's age right now.
--The function divides by 365.25 to allow for leap years and uses the FLOOR function to make sure the function returns an integer.
--The function DATEDIFF(year, @dob, @yyyymmdd) doesn't work because Microsoft chose to implement
--the "year" part of the function to calculate the number of year boundaries crossed.

View 20 Replies View Related

Calculating Between Tables

Nov 15, 2005

Hi All,

I have a very complicated project and for some reason I have decided to use SQL server as the backend database. I am new to it and have encountered my first problem. In basic terms what I want to be able to do is increment/decrement a field in a table according to something that happens in a different table. Where do I start?

So for example, a stereo system/cd player can have 10 cds at one particular time, when 2 cds are removed, then 8 slots are available. Then when 1 of the 2 remaining slots are filled then 1 slot remains and so on....so I want some sort of a counter field. Using that field I want to be able to generate a report on what is currently available.


Please Excuse my lame example but can anyone please help?

Thanks

Ben

View 1 Replies View Related

Calculating Interval

Mar 16, 2006

If two records containing datetime stamps are SELECTed, how can the interval between them be quickly calculated?

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved