Update Column With Percentage Of Total For Each Row
Dec 9, 2011
I'm trying to work out the value of a item as a % of the group.Using my simplified table below (table called sweetie)
Date Name Sweets
01/11/2011Pete5
01/11/2011Paul10
01/11/2011Paddy15
02/11/2011Pete2
02/11/2011Paul4
02/11/2011Paddy6
I would like to select a column called PERC, which would be the sum of Sweets/sum of Sweets(grouped by date).So for example for the first value I would have 5/(5+10+15).I can write the query so it totals all the sweets, but I want to use totals by date and am unsure how I would do this..My query so far is (but this doesn't use totals by date)
Date NameSweetsPERC
01/11/2011Pete5 0.12
01/11/2011Paul10 0.24
01/11/2011Paddy15 0.36
02/11/2011Pete2 0.05
02/11/2011Paul4 0.10
02/11/2011Paddy6 0.14
How can I get it to calculate based on the sum by date. Would I need to declare a variable for each date, that variable being the sum of sweets?
View 4 Replies
ADVERTISEMENT
Mar 27, 2007
Hi. I am looking to create a Matrix-based Report in SQL Server 2005 Reporting Services. I have my query, and a dynamic number of both rows and columns. I have Totals on the "bottom" and "right".
What I'd like to do is add a Percentage column to the right of the right-based Totals column. So the columns would read Col1, Col2, .., ColN, Total, Percentage.
Is this possible???
Thanks.
View 12 Replies
View Related
May 31, 2015
How can I add a Total Column as the last column in this query and also add a total column to the bottom row of the query? Then after the total column on the right, add a % column. So my expected returned set would be like so:
Location
Train
Bat
Ball
Nuggets
Total
%
[code]..
View 12 Replies
View Related
Jul 22, 2015
Calculating % of Column Total in Charts...
Doing this exercise in Excel is always very simple:
View 3 Replies
View Related
Sep 27, 2007
I'm supposed to generate a report like this.
Breakdown by Remaining Term
Balance
Balances as a %
0 thru 1 mos
1 thru 2 mos
2 thru 3 mos
3 thru 4 mos
4 thru 5 mos
5 thru 6 mos
6 thru 7 mos
7 thru 8 mos
8 thru 9 mos
9 thru 10 mos
10 thru 11 mos
11 thru 12 mos
Total
I'm wondering how to calculate the Balances as a %. Because I dont know how to calculate the Total for 12 months.
Basically Balances as a % = (Balance for 0 thru 1 mos / Balance for whole 12 mons)%
But the problem is calculating the total balance for 12 mons.
Can any one help me please?
View 6 Replies
View Related
Oct 19, 2007
Hi All,
I have to create a table like this using SSRS.
LoanStatus NoOfLoans Amouunt Percentage
Started
1,000
1,000,000.00
100%
Sent to Partner
350
350,000.00
35%
Denied by Partner
50
50,000.00
5%
Accepted by Partner
300
30,000.00
30%
Loan Apps Pnd Disb
10
100,000.00
1%
Loan Apps Disbursed
290
290,000.00
29%
Table is grouped by loan status.
Percentage is calculate as NoOfLoans/TotalNoOfLoans.
Please can anyone tell me how to calculate the grand total and Percentages?
View 5 Replies
View Related
May 4, 2015
I have 2 columns 1) Total Premium and 2) New-Renew Indicator in my Powerpivot.
The requirement is to show the a) New Premium as a Percent of Total Premium and b) Renew Premium as a percentage of Total Premium. Here is what i did:
a) Created a calculated measure called Percentage:= Divide(Total Premium, Total Premium, 0) . The percentage shows 100% as expected.
b) Now when i try to bring in the Column 2)New-Renew alongside this Percentage in the pivot table, both New and Renew shows 100%. I only have about 20 percent rows with New, and 80% of Renew.
When i bring in the original column = 1) Total Premium, the new-renew split shows correctly, just the percentage is not splitting up correctly. How to achieve it?
View 4 Replies
View Related
Jun 22, 2007
Hello,
I have a matrix that looks as follows:
Name Jan Feb Total
John 5 6 11
Mary 3 4 7
Total 8 10 18
I want to add a percent column to the RIGHT of the total, and also on the bottom row. I can't find any clear examples of how to do this. If I had a new column, it adds additional headers beneath my top row. Or, my columns appear to the LEFT of the data, not the right. Can some please post some simple instructions that will make my simple matrix look like this:
Name Jan Feb Total %
John 5 6 11 60%
Mary 3 4 7 40%
Total 8 10 18 100%
% 40% 60% 100% 100%
I am so stuck on this I can pull my hair out.
Thanks!
Michael
p.s. I really hope the next version of SSRS has a simple "Sub-total %" option that you can enable just like the sub-total column.
View 3 Replies
View Related
Aug 27, 2013
I have a column Bill_amount from bill_details table , i want to find 100%,30% of that column.
View 2 Replies
View Related
Jan 26, 2007
Hi,
I would like to generate following table with reporting service matrix.
But if I use the expression : percentage coulmn=sum(field!qty.value) / sum(field!qty.value, "region_group")
It will become a percentage of a row total, instead of a group total (store group). Please see the second table.
Any ideas?
Thank you!
(Correct!)
store1 store2 level1 level2 level3 sub total level1 level2 subtotal category region qty % qty % qty % qty % qty % qty % qty % C1 APAC 10 10% 20 20% 70 70% 100 100% 25 50% 25 50% 50 100% EURP C2 CHINA
(Wrong) store1 store2 level1 level2 level3 sub total level1 level2 subtotal category region qty % qty % qty % qty % qty % qty % qty % C1 APAC 10 6% 20 13% 70 46% 100 25 16% 25 16% 50 EURP C2 CHINA
View 1 Replies
View Related
Apr 2, 2015
This is on SQL Server 2008. Please find a detailed description and the file of the data, that I am working on.
Requirements:
1.
If
'Channel'
is
not
equal to "Omnibus"
where
the 'Trans Description'is
equal to "Purchase"
and
"Redemption"
for
one purchase and
one redemption that match on 'System'
,
'Account TA Number'
,
'Product Name'
,
'Settled Date'
,
and
where
the 'Trade Amount'
of the purchase and
redemption is
within 5%,
then
display those set of records.
2.
If
deemed wash trades,
allow user to update the purchase and
redemption pair 'Trans Description'
from
"Purchase"
to "Exchange In"
and
'Trans Description'
from
"Redemption"
with
"Exchange out"
System Channel Dealer Name Firm Name Product Cusip Product Name Product Share Class Trade ID Settled Date Account TA Number Trans Description Trade Amount
SCHWABPORTAL US - ASG MILLIMAN MILLIMAN 64128K777 Strategic Income Fund A 29806259 30-Jan-15 000BY00F2RW Redemption $ 25,68,458.15
[Code] .....
View 36 Replies
View Related
Mar 13, 2015
I have below records coming in from source files
ProdName Amount TranType
P1 100 A
P1 100 S
P2 200 A
P2 205 S
In case the ProdName is same, and Amount = or (within +/- 5%) of Amount, I have to update the TranType column as IN/OUT respectively as shown below in the tables.
I am okay with using 2 different tables if needed as in the records comes in one table and then i can reference that table to upload the values in another.
ProdName Amount TranType
P1 100 IN
P1 100 OUT
P2 200 IN
P2 205 OUT
The order of the records coming in can be different order, they need not be subsequent.
This is happening in SQl Server 2008.
View 3 Replies
View Related
Jul 27, 2006
Hello folks,
I am stuck at a problem, not sure on how to go about writing a query that will return as a percentage the number of fields in a row that are null.
For instance, a row from my table:
Row1 : field1 field2 field3
If field3 is empty or null, my query should return 67%.
So far I have gotten the number of fields:
select count(1) from information_schema.columns where table_name='myTable'
I could loop through the fields but I am sure there is a simpler way of doing it, I have seen something simpler in the past with some builtin SQL functions. I am using MS SQL 2005.
Thanks for your help
Mike
View 2 Replies
View Related
Mar 16, 2015
i made a procedure to the below output now i need to add column percentage depends on these output
block Response Heads Dept Actuals
1Sales Sales01.Sales (net of Sales Tax)1087.5999999999999
1Finance Sales02.LESS:-EXCISE DUTY 22.800000000000001
1Sales Sales03.Net Sales 1064.8
2HR HR 04.Personnel Cost 170.60000000000002
3Materials & productionCOGS 05.Material Cost 376.70000000000005
(.i.e)
block Response Heads Dept Actuals percentage
2HR HR 04.Personnel Cost 170.60 170.60*100/1064.8(dept=03.Net
Sales)
3Materials & productionCOGS 05.Material Cost 376.70 376.70*100/1064.8(dept=03.Net
Sales)
How to write make a query for that?
View 4 Replies
View Related
Sep 18, 2015
We have a table to 100M rows and up until now we were fine with an non clustered index a varchar(4000) because we never went above 900 bytes (yes it is a bad design).We have the need to support international character sets now so the column was updated to nvarchar(4000) and now we have data past the 900 byte limit.
The data is long, seems useless but is needed by the business and they need to be able to search "where bigcolumn like 'test%'". With an index, even with a huge amount of data, it was 'fast'. Now of course without an index it is unusable. The wildcard is always at the end of the search. I made a full text index on the column and basic queries such as: select * from ourtable where contains(bigcolumn, 'AReallyLongStringofTextHere') works fine unless there is a space in the data. We loose thousands of returned rows because of spaces in the data.
I have tried select * from ourtable where contains(bigcolumn, '"AReallyLongStringofTextHere that includes spaces"') but not all of the data is returned. I get 112 rows with the contains statement. The table scanning statement of "select * from ourtable where bigcolumn like 'AReallyLongStringofTextHere that includes spaces%' returns 1939 rows.I understand that a full text index is breaking the long string up since it contains spaces. Is there a way to retain the entire string as 1 index entry or is there a way to fix my query to return all of the rows?
View 9 Replies
View Related
Dec 1, 2007
i have a report with contains preview of percentage columns example of percentage of student marks in perticular subject like 95%. and if suppose any student not attend any test i have to dispaly like not attended statement.
so i have display two fields like 95% and not attended statement in same column, i given Cstr(Fields!Data.Value), it gives two fields with contains not attended statement of perticular query and it dispalys 0.95 % . but i need 95% and not attended statement for perticular query in same column.
is there any solution for dispalying string and percentage values in single column for given perticular query and those two values are disply same result compare with preview at the time of export to excel sheet
plese send solutions ASAP
Thanks
James vs
View 1 Replies
View Related
Oct 13, 2006
I have a Monthlyexpense column. How do I Sum up this column and put the Total in my ytdexpenses column. I think I figure out the query, but I don't know where to write the query at. Please help.
View 3 Replies
View Related
Jun 6, 2006
I have been working on a website in asp.net1.1 in vb.net2003. I am using a sql2000 server. I am attempting to add a column to my datagrid that will add the total number of wins and output the number in that colum. With some help, I have been able to write the code. However, I am not sure where to put it. Is it a sql function I need to call from my code to add to the win column? Thanks for your help.
View 1 Replies
View Related
May 16, 2002
This is a very simple select but I would like to add a final column that adds the QOH, QOB, QOO and Quantity. How can I accomplish this? Thanks
SELECT DISTINCT
zcus_MM_Medsurg_Used.stock_no,
zcus_MM_Medsurg_QOH_Only.qty_on_hand AS QOH,
zcus_MM_Medsurg_Used.QOB,
zcus_MM_Medsurg_Used.QOO,
zcus_MM_Medsurg_Used.QUANTITY
FROM
zcus_MM_Medsurg_QOH_Only INNER JOIN
zcus_MM_Medsurg_Used ON
zcus_MM_Medsurg_QOH_Only.stock_no = zcus_MM_Medsurg_Used.stock_no
View 1 Replies
View Related
Jul 18, 2014
I want calculate to my total column sum
ex- col 1 col 2 col 3 col 4
raj 10 20 20
pani 20 88 88
tiger 87 78 89
john 77 77 77
---------
??
----------
View 1 Replies
View Related
Jul 14, 2015
I have a table like below, which is contain area, zone, branch wise value. In that i want to get company total as a column and area total as a another column and zone total in next column
AREAZONEBRANCHVALUE
TNCENTRALAMB100
TNCENTRALTNF100
TNCENTRALSDGD100
TNCENTRALERW100
TNNORTHAMB50
[Code] ....
My expected output is like below
AREAZONEBRANCHVAL comtot areatot zonetot
TNCENTRALAMB1001200560400
TNCENTRALTNF1001200560400
TNCENTRALSDGD1001200560400
[Code] ...
I achieved this by using query but its will affect performance because my real time scenario dealing with laks of records
select a.*,b.company_tot,c.area_tot,d.zone_tot from FESIBILITY_CHECK a cross join (select SUM(value)company_tot from FESIBILITY_CHECK )b
join (select SUM(value)area_tot,area from FESIBILITY_CHECK group by area)c on a.AREA = c.area
join (select SUM(value)zone_tot,area,zone from FESIBILITY_CHECK group by area,zone)d on a.AREA=d.area and a.ZONE = d.ZONE
So I planned to use cube and roll up but i don't get desired result ...
View 8 Replies
View Related
Dec 3, 2007
Hello,
Is there a maximum total of columns that SQL Server 2005 limits? I have a rather lengthy import file in regards to column count, and was wondering if there was a total number of columns that a table is limited to.
Thanks.
View 3 Replies
View Related
Feb 23, 2015
This SQL Select below doesn't show the total value. Always shows NULL. Why?
SELECT Ano,
FORMAT((QPivot.[1]),'##,##0.00','pt-BR') As trimestre1,
FORMAT((QPivot.[2]),'##,##0.00','pt-BR') As trimestre2,
FORMAT((QPivot.[3]),'##,##0.00','pt-BR') As trimestre3,
FORMAT((QPivot.[4]),'##,##0.00','pt-BR') As trimestre4,
Municipio,
[Code] ....
View 4 Replies
View Related
Jan 23, 2015
Error Message "invalid column name on total_counter ". How to edit this query and make it work ?
SELECT salesman_code,user_group,user_name,trx_acc_amt,trx_date,trx_no,sh_code,
salesman_code4,salesman_code5,salesman_code6,
Sum(case when salesman_code4 is null then 0 else 1 end +
case when salesman_code5 is null then 0 else 1 end +
case when salesman_code6 is null then 0 else 1 end) as total_counter,
[code]...
View 5 Replies
View Related
Nov 2, 2007
Hi Everyone,
I am designing a report in SQL Server 2005 Report Designer that uses a Matrix table. The matrix table is grouped by month. How can I add a Year-To-Date total column on the report? This is driving me nuts...I cannot figure this out.
Thank You
-Sam
View 1 Replies
View Related
Jan 11, 2007
<P>Greetings,</P>
<P>I am new to reporting services and am struggling with trying to add a column to the end of matrix report that has totals. You can see a jpg of the report at http://www.catertots.com/matrix.jpg What I need to do is repeat the school code that is in the first column into another column that follows the total. </P>
<P>Any help would be much appreciated.</P>
View 3 Replies
View Related
May 23, 2015
I have the below query
DECLARE @GivenDate DATE='2015-05-13'
create table #table (LedgerID int,AttDate Date, checkedtime time,checkedtype varchar(1))
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','09:01:48.0000000','I')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','13:05:52.0000000','O')
insert into #table (LedgerID,AttDate,checkedtime,checkedtype) values (1232,'2015-05-13','14:10:25.0000000','I')
[code]....
the result is like below
i need 'TotalDiffernceTime' column as new column (OUT1-IN1)+(OUT2-IN2).
i am using SQLServer 2008 R2
View 8 Replies
View Related
Feb 1, 2007
Hi,
I have created a matrix with multiple rows in the main data cell and a subtotal at the end of the row. The first row in my matrix main cell is just a count of records, whereas the 2nd row is a % of the value in the 1st row compared to the total of that row. I have 5 columns in the matrix as below (only first row is shown plus the total row. Ignore any rounding issues):
Status
A
B
C
D
E
Total
02/01/2007
No.
9
32
3
13
0
57
% Total
15.00%
56.00%
5.00%
24.00%
0.00%
100.00%
Total
No.
77
143
25
72
2
319
% Total
135.00%
350.00%
48.00%
250.00%
2.00%
556.00%
I have a total column at the end. I want to hide the %Total row within the total row as it does not make sense in this context. Anyone know how to achieve this?
Thanks
View 4 Replies
View Related
Jan 15, 2008
Hi,
Hi All,
I am having some trouble getting a Grand Total column at the end of my matrix report (not row at the bottom), such as you get by default in an Excel pivot table.
I have managed so far to add another column that sums up all values across the data range for a particular product code. My intention was to hide all but the last (I did something similar on rows). However you cannot use an expression on the width property for columns, so this will not work.
My report is very standard:
Year
Month (grouped by year)
Product code | Description [qty]
I want a column that displays the total qty across all dates for each row. Surely this is possible?
Any suggestions would be appreciated! Thanks.
View 6 Replies
View Related
Nov 9, 2015
I am trying to calculate the the running total but also tried to reset to reset to zero based on a value of a column.
here I am trying to calculate the value of CalcVal column based on column Flag value...actually it is running total but it reset to zero if Flag value is 0.
Here is the example of data along with required column
MonthId Flag CalcVal
201401 1 1
201402 1 2
201403 1 3
201404 1 4
201405 1 5
and so on .........
View 8 Replies
View Related
Sep 7, 2007
Hello Guys,
I am working on a matrix report which has several row groups and 1 column group. After execution, the column group wil end up with several columns containg numeric counts. I would like to have the grand total for each "column group" column as a last row on this report.
For row groups you can just right click "Subtotal", but that is not possible for column group. Could someone please help me to find a clever way of accomplishing this, please. Thank you so much for your help!
View 7 Replies
View Related
Jun 3, 2004
I am trying to create a procedure which will calculate the total tuition
This process involves 3 tables.
Contract table has tuition information which is all $100 (set price).
Discount table has discount type and discount percentage (ex. 0.3) on each discount type.
ContractDiscount table have contract number and discount number to connect both tables
I think I need to create a loop since some contract gets more than one discount.
I have to calculate and get result nee to be like this
total_tuition = (tuition - discountPer * tuition) - this has to be a loop condition
Do you have any suggestion ?
Thanks
View 3 Replies
View Related
Jul 14, 2014
How can i get the total number of each distinct value in the column and write it to another table e.g.
MyTable
Id Fruit
1 Apple
2 Banana
3 Apple
4 Watermelon
5 Banana
6 Watermelon
7 Apple
Result
Fruit Count
Apple 3
Banana 2
Watermelon 2
View 9 Replies
View Related
