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:
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)
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.
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
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 ...
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
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?
I insert/update the figure information in this table with daily figures - ie if no record exists insert new name and figure, if figure exists, update figure. I have been asked to add logic in the insert/update SP to add a fee of 0.25% to any daily figure that results in the total value in the base table being over 1000.
For example: Base table value is 900 before update ----- Daily figure is 200 so 900 + 200 = 1100 after update of base data. New logic dictates that 0.25% must be added to 100 of this daily figure, as 100 brings it up to 1000 and the other 100 (which makes the 200) takes it over the 1000 threshold. 100 + 0.25% = 0.25 ------- Total value to add to base table = 100 + 100 + 0.25 = 200.25.i am keen to avoid WHILE loops and cursors..
I totally forgot how can I make a percentage. Look at the code below:
create table #acca (name varchar(10), tipo varchar(10), lett int) insert into #acca values ('Italy','Europe',15), ('France','Europe',10), ('Colombia','America',15), ('Cile','America',75) select * from #acca
/*Query Number 1 */ select name, tipo, lett, sum(lett) over (partition by tipo) as TotCon, sum(lett) as Total from #acca group by name,tipo,lett;
/*Query Number 2*/ with cte as ( select name, tipo, lett, sum(lett) over (partition by tipo) as TotCon, sum(lett) as Total from #acca group by name,tipo,lett) select name, tipo, lett/totcon from cte
Question query number 1: how can I retrieve the absolute total? Sum(lett) over what? Question query number 2: why lett / totcon retrieves 0? Question plus: is there a way to retrieve the percentage without using the cte?
Below is my SQl which just counts the number of appointments and grouped by clinic. This is great but what I'd like to add is the percentage within each clinic.
For example Clinic BRESRAD1 has a total of 61 appointments, of which 75.41% are Normal Appointments and 24.59% are Diagnostic, Ideally I would like the percentage in the next column.
BRESRAD1 Normal Appointment 46 BRESRAD1 Diagnostic Appointment 15 BRESRAD2 Normal Appointment 17 BRESRAD2 Diagnostic Appointment 12 BRESRAD3 Normal Appointment 34 BRESRAD3 Diagnostic Appointment 43
My SQL is as follows:
SELECT ClinicCode, CASE WHEN [ApptTypeDesc] LIKE '%Diag%' THEN 'Diagnostic Appointment' ELSE 'Normal Appointment' END AS [Diagnostic Appt], COUNT(OPAppointmentID) AS CountOfOPAppointmentID FROM dbo.OP_APPOINTMENT WHERE (AttendStatusNatCode IN ('5', '6')) AND (ApptFinYr = '2014/15') GROUP BY ClinicCode, CASE WHEN [ApptTypeDesc] LIKE '%Diag%' THEN 'Diagnostic Appointment' ELSE 'Normal Appointment' END ORDER BY ClinicCode
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
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.
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?
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.
I tried my syntax below, but it said that the field were not in the group by. This is syntax and for each of the 2 names (only small subset of real data) I need a Total column to display between each different person like so:
My query below displays a total row, but it displays above the city, I want it to display below the city. But now their is an additional caveat, I have a table that sets the order each city & total row should display in. How can that be added to my query to set the display order?This is what I have that displays the total above the city, could this be ammended to join in the order table and display in that order? SQL Server 2008
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?
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
I need to get a cumulative total for row by row basis. I need this grouped on name, id, year and month. ID is not a auto incremented number. ID is a unique number same as name. The out put I need is as below,
Name ID Year Month Value RunningValue XX 11 2013 Jan 25 25 xx 11 2013 Feb 50 75 yy 22 2015 Jan 100 100 yy 22 2015 Mar 200 300
How I could get this query written? I am unable to use SQL Server 2012 version syntaxes. Writing a cursor would slow the process down because there is a large data set.
I have a question regarding the total count of the table rows
Select count (name) from test. Lets say I have got 200 count. And Select count (lastname) from test1.I have got 200.And this counts I should store it in "There are nn name items awaiting your attention and nn pending lastname's awaiting your approval".
In the below query want to display total of company expenses. I am unable to get the sum of weeklycomexpenses
SELECT DISTINCT e.EmpID, e.EmpName, e.StartDate, e.EndDate, ee.WeeklyComTotalExpenses FROM EMPArgentTimeSheet AS e LEFT OUTER JOIN (SELECT CExpID, WeeklyComTotalExpenses FROM CompanyArgentExpenses GROUP BY CExpID, WeeklyComTotalExpenses) AS ee ON ee.CExpID = e.EmpID where EmpName = 'Eberhard Neumann' and (e.StartDate >='8/1/2015')AND (e.EndDate <= '8/31/2015') order by EmpID desc
Is it possible to assign to a variable, then add to it later on? When I run the below, all I get is 3 rows affected I never see the value printed. What i am wanting to do is each loop sum the numbers so 2+1+3 =6 so in the end @sumofallnumbers = 6 Create Table #Test ( randarnumbers int ) Insert Into #Test Values ('2'), ('1'), ('3') Declare @sumofallnumbers int, @nbr int Declare c1 Cursor For Select randarnumbers FROM #Test
I am using the below script to get space alerts and now i am interested in sending alerts if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %
I need to create a stored procedure for total count of the user's. If User from front end invites other user to use my tool, that user will be stored into a table name called "test",lets say it will be stored as"Invited 1 User(s)" or if he invites 2 users it will store into table as "Invited 2 User(s)."
But now we have changed the concept to get the ISID (name of the user) and now when ever the user invites from the front end, the user who have invited should stored in two tables "test" and " test1" table .
After we get the data into test and test1 table i need the total count of a particular user from both tables test and test1.
if i invite a user , the name of the user is getting stored in both test and test1 tables.Now i want to get the count of a user from both tables which should be 1,but its showing 2.
Reason: Why i am considering the count from 2 tables is because before we were not tracking the usernames and we were storing the count in single test table.,but now we are tracking user names and storing them in both tables(test and test1).
Here is my sample code:
I need to sum it up to get the total user's from both the table but I should get 1 instead of 2
SELECT (select distinct COUNT(*) from dbo.test where New_Values like '%invited%' and Created_By= 'sam' + (select distinct count (*) from dbo.test1 where invited_by ='sam'
I have a script that produce a result set that is almost complete. I have a new requirement come up to put a Total at the button of the result set of my Query.
I'm using SQL Server 2008 ;WITH CTE AS ( SELECT Case WHen left(MONTHDate,3)='Jan' Then '01' WHen left(MONTHDate,3)='Feb' Then '02' WHen left(MONTHDate,3)='Mar' Then '03' WHen left(MONTHDate,3)='Apr' Then '04' WHen left(MONTHDate,3)='May' Then '05'