Summing Running Totals

Apr 22, 2008

Hi There,

I have the following query:

SELECT NATNLACCT, IDCUST, TEXTSNAM, AMTBALDUEH
FROM
VIEW_ARCUS
where amtbaldueh != .000
order by NATNLACCT

but i want to display the data something similar as below. How do I create a column to display with the natinlacct name(I have many) concatenated to Total and then sum amtbaldueh?



Natinlacct idcust textsnam amtbaldueh


Doda 1234 abcd $101

Doda 5678 efgh $200
Doda 9876 ijkl $300

Doda Total $601

Nava 5847 jhgf $230

Nava 5487 lfde $130
Nava 3587 lrsd $100

Nava Total $460

Thanks
Rhonda

View 5 Replies


ADVERTISEMENT

Summing Up Subgroup Totals

Nov 9, 2007

In below table, the light blue rows, represents the subgroup I am trying to sum up.

The value in the "Inventory Value" contains the latest inventory value for said inventory.
Light green row is grouped by Store name, Light blue is grouped by inventory name. the expression in Inventory row for the inventory value column is =First(Fields!InventoryValue.Value).

Doing a store summation, sums up all detailed values. For example in Store A, instead of 70K, the sum would be 130K... double summing the toy soldiers and cute dolls inventory. Is there a way to do the "correct" summation or a different approach to this? Thanks!












STORE
TOTAL STORE INVENTORY VALUE
INVENTORY
% OF STORE INVENTORY
INVENTORY VALUE
WARNING DATE

STORE A
1,000,000.00







TOY SOLDIERS
2.00%
20,000.00






20,000.00
11/5/2007





20,000.00
11/2/2007



PLUSH TOYS
1.00%
10,000.00






10,000.00
11/5/2007



CUTE DOLLS
4.00%
40,000.00






40,000.00
11/2/2007





40,000.00
10/31/2007


1,000,000.00

7.00%
70,000.00









STORE B
10,000,000.00







TOY SOLDIERS
0.20%
20,000.00






20,000.00
11/5/2007





20,000.00
11/2/2007



PLUSH TOYS
0.10%
10,000.00






10,000.00
11/5/2007



CUTE DOLLS
0.40%
40,000.00






40,000.00
11/2/2007





40,000.00
10/31/2007


10,000,000.00

0.70%
70,000.00

View 4 Replies View Related

Report Services 2000 Totals On Group Are Incorrect. Summing Duplicate Values

Dec 19, 2007

Hello Everyone
I've created a report with a simple dataset that is similar to this

City , RequestID, Request Amount, ClaimID, ClaimAmount
El Monte 791 52,982.00 2157 41,143.75
El Monte 3691 11,838.00 3140 8,231.14
El Monte 3691 11,838.00 3141 990.00
El Monte 3691 11,838.00 3142 2,615.00


So I group by City, RequestID. On the first group I specified the expression to be City and in the header I list the city and in the footer I list the sum of Request amount. On the second group I specified the group by Request so in the header I placed requestID and on the footer I placed Request Amount. I set request information to hide the duplicates and I even add =Sum(Fields!RequestApprovedGrandTotal.Value,"GroupByRequestID") the scope of the group. But this is what I get:


For requestID = 3691 for Request Amount is 35,514.00 not 11,838.00. All the claim sums are correct and they are located on the detail row.



I've read that a work around is to create multiple dataset but I honestly believe that something as simple as this should work on the reporting server 2000. So I've come to the conclusion that I must be doing something wrong. Can someone give me a hand on this. Thanks.

View 6 Replies View Related

Running Total: Summing On Distinct Amount

May 9, 2007

I am trying to do a sum on a goal amount that is repeated for each record. But what is the the forumla to only sum on the distinct goal amount.



Example:

Month Year Goal Other Value

March 2007 500 5568

March 2007 500 5568

March 2007 500 5569

April 2007 600 5568

April 2007 700 5569



Total (I am receive)

March-April 2007 1600 5568

March-April 2007 1200 5569



Total (I excpect)

March-April 2007 1100 5568

March-April 2007 1200 5569



I haven't found anything online to help.



Thanks,

Sam

View 5 Replies View Related

Running Totals With SQL

Oct 1, 1999

I have a situation where I want to get running totals of a potentionally very large table. Say I have a million records with a table with few fields.
Table structure like this
UID value
1 3
2 9
3 4
4 7
5 2


I want to return a result set of something like this
select uid, value, (rtotal???) from table

uid value rtotal
1 3 3
2 9 12
3 4 16
4 7 23
5 2 25

This is to be used for sort of a lotery. Say I have 1 million users with a variety of points tward the lotery. I total the points, is say 5 million, determined
the winner to be say 3,234,563 Now I have to determine which uid is the winner. So I planned to to do a running total till the winning value
is less then or equal to the running total and I have my winner. I am looking for a fast way to do this on a large database.

Any Ideas?

View 1 Replies View Related

Running Totals

Jan 12, 2004

Hi Folks,

I have a problem that I know that i should be able to code up but have drawn a blank due to it being monday. Anyway......

Have a table :

create table test_registrations
(
date_maint smalldatetime,
user_name1 varchar (255),
user_address1 varchar (255),
total_users int
)
go

If i have a number of registrations on a particular date then I can tell all how many users have registered in any date by :

select date_maint , count (1)
from test_registrations
group by date_maint
order by date_maint desc
go

The qestion is how can I keep a total registared users count. Say if I have 10 users join on the 1st of Jan and 15 on the 3rd then I want
the total users for the users on 1st to read 10 and total users on the 3rd to read 25.

I know i should be able to code this up but I'm being a dumb ass. Can someone show me a way to code it. Is it some sort of correlated sub query to keep a running total ?

View 3 Replies View Related

Running Totals

May 23, 2008

On SQL Server 2005 at least, this works efficiently where we have an indexed row number.

It does seem to be very sensitive to the join condition in the recursive part of the CTE: changing it to the equivalent "ON T.rn - 1 = RT.rn" results in a scan of T each time instead of a seek!


DROP TABLE dbo.T

-- rn must have contiguous values for this to work:
CREATE TABLE dbo.T (rn int PRIMARY KEY, f float NOT NULL)

-- 100000 random floats between 0 and 1:
INSERT INTO dbo.T
SELECT n+1 AS rn, RAND(CAST(NEWID() AS binary(4))) AS f
FROM dbo.Numbers
GO

;WITH RT AS (
SELECT rn, f AS rt
FROM dbo.T
WHERE rn = 1

UNION ALL

SELECT T.rn, RT.rt + T.f
FROM RT
INNER JOIN dbo.T AS T
ON T.rn = RT.rn + 1
)
SELECT *
INTO dbo.TRT
FROM RT
--ORDER BY rn
OPTION (MAXRECURSION 0)



SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'Worktable'. Scan count 2, logical reads 600001, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T'. Scan count 100000, logical reads 200002, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 3500 ms, elapsed time = 3724 ms.

(100000 row(s) affected)

View 4 Replies View Related

Running Totals

Jun 11, 2008

Now my code below brings everything i want it too, the problems comes is I need to get a running total of sales for each day. Currently it shows the sales for a store for each day and what there projections should be. I need a running total for each day so if you were to have todays date it would have the sum(sales) between today and the first or the month that im in. but still show what the total was on the 10th, 9th, and so on.


Declare @Brand as varchar(10)
DECLARE @StartDate datetime
Declare @EndDAte as Datetime


Set @Brand = 'business'
SELECT @StartDate=CAST('1/1/'+CAST(YEAR(GETDATE()) AS varchar(4)) AS datetime)
SET @EndDate =CAST('12/31/'+CAST(YEAR(GETDATE()) AS varchar(4)) AS datetime)

Select ttProjection.StoreID,S.StoreName , ttProjection.DailyProjection, ttProjection.DAYS, ISNULL(ttSales.Sales,0) as Sales
From

/**********Finds projection per day****************/
(Select StoreID, Projection, Projection/Cast(DaysInMonth as INT) as DailyProjection, DAYS
From
(Select StoreID, Projection as Projection,
Month, Day(DateAdd(m, 1,DateAdd(d,1 - Day(Month), Month))-1) As DaysInMonth
From Reporting.dbo.RetailSalesComparison_ProjectionsView
Where StoreID between 12000 and 12999
)ttTemp

Right Join



(SELECT DATEADD(dd,number,@StartDate) as DAYS
FROM
(
select number from master..spt_values
WHERE type='p'
union all
select number+256 from master..spt_values
WHERE type='p'
) as s
where DATEADD(dd,number,@StartDate)<=@EndDate)ttDays on Month(ttTemp.Month) = Month(ttDays.DAYS))ttProjection


Left Join

(Select Date, StoreID, Sum(Sales) as Sales
From Reporting.dbo.RetailSales_byStore_byDay
Group By Date, StoreID)ttSales
on ttProjection.StoreID = ttSales.StoreID
and ttProjection.DAYS = ttSales.Date
Inner Join DelSolNet2.dbo.Store S on ttProjection.StoreID = S.StoreID
Where Month(Days) = Month(getdate())
Order By Days, ttProjection.StoreID

View 3 Replies View Related

Running Totals

Jan 19, 2007



We are trying to create a report that shows a Week to Date, Month to Date, and Year to Date

Week to Date Month to Date Year to Date

Item Number



I've tried using an if statement (if date = current week, Qty, 0) and then sum the data but I get an error message that reportitems can only be summed in the page header and footer. I've also tried running totals, but it doesn't like the iif statement. Any ideas on how to do this?



Nancy

View 6 Replies View Related

SQL Running Totals And Subtractions

Apr 8, 2008

I have a table for which I need a “special� running total. More specifically there should be a close communication between the credit and the debit column. The scenario should be in the following order
“Credit�“Debit�“Balance credit�“Balance debit�
6 0 6 0
5 0 11 0
0 4 7 0
0 9 0 2
3 0 1 0
0 5 0 4
0 2 0 6

I have to point out that this is done in a grouped form, where the CustormerID is the grouped clause. Now I have achieved this to a point where the calculations work only if the credit column is bigger than 0, but when the account starts with debit I get only sums of the column not the needed subtractions. What am I missing? Thanks in advance.
DECLARE @PrevRunBal MONEY --Overall running total
SET @PrevRunBal = 0
DECLARE @PrevGrpBal MONEY --Running total resets when account changes
SET @PrevGrpBal = 0
DECLARE @PrevRunCnt INT --Overall running count (ordinal rank)
SET @PrevRunCnt = 0
DECLARE @PrevGrpCnt INT --Running count resets when account changes
SET @PrevGrpCnt = 0
DECLARE @PrevAcctID INT --The "anchor" and "account change detector"
SET @PrevAcctID = 0
changes
SET @PrevGrpBalP = 0
update Temp

SET --===== Running Total
@PrevRunBal = RunBal = @PrevRunBal + dolguva,
-- @iznos =RunBal= dolguva - pobaruva + @sdol -@spob,
--===== Grouped Running Total (Reset when account changes)
@PrevGrpBal = Bcredit = CASE

WHEN CustomerID = @PrevAcctID
THEN CASE
WHEN CREDIt > DEBIT or CREDIT = 0
THEN (@PrevGrpBal+CREDIT)-(DEBIT)
WHEN DEBIT>CREDIT or DEBIT =0
THEN (@PrevGrpBal+DEBIT)-DEBIt
END
ELSE CASE
WHEN CREDIT >DEBIT
THEN (CREDIT)
WHEN DEBIT > CREDIT
THEN DEBIT -- restarts from 0 if only 1 rec.
END

END,


--===== Running Count (Ordinal Rank)
@PrevRunCnt = RunCnt = @PrevRunCnt + 1,
--===== Grouped Running Total (Ordinal Rank, Reset when account changes)
@PrevGrpCnt = GrpCnt = CASE
WHEN CustomerID = @PrevAcctID
THEN @PrevGrpCnt + 1
ELSE 1 -- Restarts count at "1"
END,

--===== "Anchor" and provides for "account change detection"
@PrevAcctID = CustomerID
Apologies if I'm posting in a wrong forum

Nik

View 2 Replies View Related

SQl Running Totals And Subtracting

Apr 8, 2008

I have a table for which I need a €œspecial€? running total. More specifically there should be a close communication between the credit and the debit column. The scenario should be in the following order





Credit

Debit

Balance credit

Balance debit


6

0

6

0


5

0

11

0


0

4

7

0


0

9

0

2


3

0

1

0


0

5

0

4


0

2

0

6
I have to point out that this is done in a grouped way where the CustormerID is the grouped clause. Now I have achieved this to a point where the calculations work only if the credit column is bigger than 0, but when the account starts with debit I get only sums of the column not the needed subtractions. What am I missing? Thanks in advance.
DECLARE @PrevRunBal MONEY --Overall running total
SET @PrevRunBal = 0
DECLARE @PrevGrpBal MONEY --Running total resets when account changes
SET @PrevGrpBal = 0
DECLARE @PrevRunCnt INT --Overall running count (ordinal rank)
SET @PrevRunCnt = 0
DECLARE @PrevGrpCnt INT --Running count resets when account changes
SET @PrevGrpCnt = 0
DECLARE @PrevAcctID INT --The "anchor" and "account change detector"
SET @PrevAcctID = 0
changes
SET @PrevGrpBalP = 0
update Temp

SET --===== Running Total
@PrevRunBal = RunBal = @PrevRunBal + Credit,

--===== Grouped Running Total (Reset when account changes)
@PrevGrpBal = Bcredit = CASE

WHEN CustomerID = @PrevAcctID
THEN CASE
WHEN CREDIt > DEBIT or CREDIT = 0
THEN (@PrevGrpBal+CREDIT)-(DEBIT)
WHEN DEBIT>CREDIT or DEBIT =0
THEN (@PrevGrpBal+DEBIT)-DEBIt
END
ELSE CASE
WHEN CREDIT >DEBIT
THEN (CREDIT)
WHEN DEBIT > CREDIT
THEN DEBIT -- restarts from 0 if only 1 rec.
END

END,


--===== Running Count (Ordinal Rank)
@PrevRunCnt = RunCnt = @PrevRunCnt + 1,
--===== Grouped Running Total (Ordinal Rank, Reset when account changes)
@PrevGrpCnt = GrpCnt = CASE
WHEN CustomerID = @PrevAcctID
THEN @PrevGrpCnt + 1
ELSE 1 -- Restarts count at "1"
END,

--===== "Anchor" and provides for "account change detection"
@PrevAcctID = CustomerID

View 13 Replies View Related

Adapting Running Totals To Query

Jul 8, 2013

I'm trying to adapt a formula for running totals to my Query, have some difficulties in declaring the tables correctly.

Formula: SELECT Ordinal, QTY, QTY+COALESCE((SELECT SUM(QTY)
FROM Table b
WHERE b.Ordinal < a.Ordinal),0) AS RunningTotal
FROM Table a
ORDER BY Ordinal

Now my Table is rather lengthy and is described by this query:

Select X.Ord, X.QTY
From (Select.... )X

Now in order to adapt the formula for running totals I did:Select X.Ord, X.QTY, X.QTY+COALESCE((SELECT SUM(X.QTY)

FROM X b
WHERE b.Ord < a.Ord),0) as RunningTotal
From (Select....)X a
Order by X.Ord

It gives my an ncorrect syntax error near a. I guess I miss the (double) declaration of the Table.

View 4 Replies View Related

Calculat Running Totals Column

Jul 13, 2006

Hi I have a table with the following structure:

UserID__DateJoined

I want to produce a query that shows on a month/year bases the number of users joined for each month AND show a running total of membership, I'm almost there....just got a problem getting the running totals:

this is what I have so far:

SELECT DATEPART(MONTH, DateJoined) AS Month, DATEPART(YEAR, DateJoined) AS Year, COUNT(UserID) AS NuAccounts
FROM Accounts_Users
GROUP BY DATEPART(MONTH, DateJoined), DATEPART(YEAR, DateJoined)
ORDER BY DATEPART(YEAR, DateJoined), DATEPART(MONTH, DateJoined)


How do I produce a column that shows the running total of accounts?

thanks for any help

View 6 Replies View Related

Transact SQL :: Running Totals In A View

Sep 14, 2015

I have a ms-sql 2012 for this task. I read that running totals / sum over is better supported here.

There are customers with an account where they can insert or withdraw cash. When they insert, the inserted amount will get a date where it expires. After the expiration date, the cash becomes unavailable.

I'm not sure how to design the tables to meet my needs, but this is my best guess. And below is the first problem in the view.

Table: deposit    This table will hold all deposits

customerId    (string)
balanceType    (string)
transactionDate   (date) 
expiresOnDate   (date)
amount     (decimal)
transactionText   (string)

The data set for the deposit table could be:

1 Bonus 01-05-2015 30-04-2016  500  Inserted 500
2 Bonus 01-05-2015 30-04-2016  500  Inserted 500
3 Bonus2 01-01-2015 31-12-2015  100  Inserted 100
2 Bonus2 01-01-2015 31-12-2015  100  Inserted 100

Table: withdrawal  This table will hold all withdrawals

customerId    (string)
balanceType    (string)
transactionDate   (date) 
amount     (decimal)
transactionText   (string)

The data set for the withdrawal table could be:

2 Bonus2 01-04-2015 -100  Needed 100
2 Bonus2 02-04-2015 -100  Needed 100
2 Bonus2 03-01-2015 -100  Needed 100
3 Bonus2 10-04-2015 -50   Took out 50
3 Bonus2 11-04-2015 -100  Took out 100

[Code] .....

Now I need to combine the two tables in a view with a running total, ordered by customerId, balanceType and transactionDate

customerId
balanceType
transactionDate
amount
Total
Text

The view must show this, when selecting all records:

1 Bonus 01-05-2015 500  500  Inserted 500
1 Bonus 01-07-2015 -100 400  Yes, got 100
1 Bonus 02-07-2015 -100 300  Yes, got 100
1 Bonus 03-07-2015 -100 200  Yes, got 100

[Code] ....

And a last view that will show distinct totals for each customer and balanceType

customerId
balanceType
totalBalance

1 Bonus -200
2 Bonus -300
2 Bonus2 -200
3 Bonus2 -550

View 8 Replies View Related

SQL Server 2012 :: Running Totals With OVER Clause?

May 28, 2015

A while back, a "quirky update" method was proposed for lightning fast running totals based on the three-part MSSQL UPDATE's SET statement and tally tables. However, some claimed this was not 100% absolutely guaranteed behavior.

How does the new OVER clause compare in terms of performance ?

DECLARE @Tbl TABLE
(
pk int not null primary key identity,
N int
)
INSERT INTO @Tbl (N) SELECT TOP 1000 1 FROM syscolumns a CROSS JOIN syscolumns b
SELECT pk, SUM(pk) OVER (ORDER BY pk )
FROM @Tbl

View 9 Replies View Related

SQL Server 2012 :: Running Totals For Previous X Days?

May 13, 2015

get the desired results for the following sample data set. I was able to come up with a query that returns the the expected results however only for a given day, so I'd need to union several select statements the get the desired results which is definitely not ideal. I'd like to pass a parameter in (number of days) instead of doing a unions for each select.

DECLARE @T TABLE (Id INT, Category VARCHAR(1), [Date] DATE)
INSERT INTO @T
SELECT 1 AS Id, 'A' AS Category, '2015-5-13' AS ActivationDate UNION ALL
SELECT 1, 'A', NULL UNION ALL
SELECT 1, 'A', '2015-5-13' UNION ALL
SELECT 1, 'A', NULL UNION ALL

[code]....

View 9 Replies View Related

T-SQL (SS2K8) :: Calculating Running Totals For Partitions Of Data

Sep 23, 2015

I have table named #t1 and three columns. CODE, Column1, and Column2.

create table #t1 (
CODE NVARCHAR(20),
COLUMN1 NUMERIC(18,2),
COLUMN2 NUMERIC(18,2)
)

And i have some data:

INSERT INTO #t1 (CODE,COLUMN1,COLUMN2)
VALUES ('432', 0,100),
('TOTAL FOR 432',0,100),
('4320001',0,250),
('4320001',50,0),
('4320001',0,140),
('4320001',300,0),
('TOTAL FOR 4320001',350,390),
('432002',200,0),
('432002',0,100),
('TOTAL FOR 432002',200,100)
drop table #t1

I want to have 4 column (named BALANCE). Balance must be column that represent running totals between two columns (Column1 - Column2) for each group of data. For each group total must start from zero.after total 432 it starts to count again for total 4320001 and again for total 432002. I'm using MS SQL SERVER 2014.

View 9 Replies View Related

Running Totals In A Report Builder Column Chart

Mar 9, 2006

I've created a Report Builder Column chart with months as the x-axis
(category) and revenue as the y-axis (value). This correctly displays
the revenue for each month, but I'd like to display a running total of
the revenue for the year.

I'm currently displaying:
Jan $2
Feb $1
Mar $4


I want to display:
Jan $2
Feb $3
Mar $7


I know this is possibly using the RunningTotal formula in Reporting
Services, but I'd like to do this in Report Builder so the users can
change and create their own graphs with running totals. Is there a way to do this in Report Builder?

Thanks.

View 1 Replies View Related

Transact SQL :: Query For Month Wise Running Totals Of Sales Amount?

Nov 28, 2012

I have a sales tables which looks as below.

DEPARTMENT
Barnd_Name
Item_Group
     S_DATE
          S_AMOUNT
Administration
IBM

[code]....

Now i need Month Wise Running Totals.but i should check the following group as show below i that order

1) DEPARTMENT
1) Brand
3) Item Group
4) Month

View 12 Replies View Related

Reporting Services :: Calculating Grand Totals From Group Totals

May 9, 2015

I have some data grouped in a table by a certain criteria, and for each group it is computed a subtotal for the group. Of the values from each of the group, I want to create a grand total on the report by adding every subtotal from each group.

Example:
...
....
Group1              Value
                           10
                            20
Sub Total 1:         30

Group2                 Value
                              15
                              25
Sub Total 2:           40

Now, I would like to be able to add subtotal 1 (30) to subtotal 2 (40) and my grand total would be 70. Can I accomplish this task in SSRS?

View 5 Replies View Related

Trying To Get Daily Totals From Cumulative Totals In A Pivot

Oct 2, 2006

I have been providing sales data for a few months now from a table that is set up like this:

Date WorkDay GasSales EquipmentSales

9/1/2006 1 100.00 200.00

9/4/2006 2 50.00 45.00

etc.

As can be seen, the data is daily, i.e., on the first workday of September we sold one hundred dollars in gas and two hundred dollars in equipment. On the second workday of September we sold fifty dollars in gas and forty-five dollars in equipment.

Now, however, the data I have to pull from is cumulative. So, using the last table as an example it would look like this:

Date_WorkDay_GasSales_EquipmentSales

9/1/2006 1 100.00 200.00

9/4/2006 2 150.00 245.00

etc.

To make things more complicated, the powers that be wanted this data presented in this fashion:

Total Sales:

1_2_etc.

300.00 95.00 etc.

 So, I have been doing a pivot on a CRT to get the data to look like I want. The code is like this:

with SalesCTE (Month, WorkDay, [Total Sales])

as

(

SELECT

datename(month, cag.date),

cag.WorkDay AS [Work Day],

sum(cag.sales_gas + cag.sales_hgs) AS [Total Sales]

FROM CAG INNER JOIN

Branch ON CAG.[Oracle Branch] = Branch.OracleBranch

group by cag.date, cag.WorkDay

)

select * from SalesCTE

pivot

(

sum([Total Sales])

for WorkDay

in ([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])

) as p

So, my question is:

How do I get the data to give back daily totals instead of the cumulative amounts for each workday? If the query was a simple one, I'd do something like

select [1] as [Day 1], [2]-[1] as [Day 2], [3]-[2] as [Day 3], etc.

but the query is far from normal, with the CRT and the pivot. I can't seem to get it to work how I'd like.

Any advice/answers? Thanks in advance!!!

 

P.S. I don't know how to get it to quit with the freakin' smileys.... I suppose you can figure out what my code is really supposed to look like above. Needless to say, it doesn't include a devil face and a damn music note...

View 12 Replies View Related

Summing

Jul 20, 2005

I am trying to check a list (MyList) against another List(SupplierList).I want sum the Qty's of UniqueID on MyList and extract the sum of thesame UniqueId's on SupplierList.BTW There are more than one instances of Unique Id on each list.The Script below is providing me with the correct answer for someproducts (UniqueId), but incorrect amounts for others.The incorrect answer is always a multiple of the correct answer.What am i doing wrong???Regards,CiaránSELECT MyList.[Unique ID], SupplierList.[Unique ID], Sum(MyList.[SHP_QTY]), Sum (SupplierList.[Qty new])FROM MyList LEFT OUTER JOIN SupplierList ON MyList.[Unique ID]= SupplierList.[Unique ID]GROUP BY MyList.[Unique ID], SupplierList.[Unique ID]

View 1 Replies View Related

Summing With A Sub-query?

Apr 30, 2008

Hi,

I'm trying to get a sum but not doing too well. I think I need a subquery but am unsure how to phrase it.

Problem:
I need to sum timesheet hours logged at work-code level to project-level (for named projects), where a project consists of 0-to-many work-codes. The 'Project' table is used for both projects and work-codes; the 'pr_code' contains the unique code (i.e. the work-code or the project-code), 'pr_master' field contains the parent. The Timesheet table will contain pr_code's for work-codes, but won't contain an entry for a work-code if no-one has logged any time to a work-code.

Sample input:

Timesheet table
===============
pr_code|ts_hours
QWER.01|6
QWER.01|7
QWER.02|3
QWET.01|2


Project table
=============
pr_code|pr_master
QWER.01|QWER
QWER.01|QWER
QWER.02|QWER
QWET.01|QWET
QWER|QQQQ
QWET|QQQQ
QWEY|QQQQ



Intended output:
For named projects QWER, QWET & QWEY:

QWER|16
QWET|2
QWEY|0



I've got the following so far which almost gets there, but appears to be summing up as it goes i.e. QWER=16, QWET=18, QWEY=18:


SELECT p1.PR_Master AS Expr1, SUM(Timesht.TS_Hours) AS Expr2
FROM Timesheet LEFT OUTER JOIN
Projects ON Timesheet.PR_Code = Projects.PR_Code LEFT OUTER JOIN
Projects p1 ON Timesht.PR_Code = p1.PR_Code
WHERE (p1.PR_Master IN ('QWER', 'QWET', 'QWEY'))
GROUP BY p1.PR_Master


Any help most appreciated.

View 5 Replies View Related

Summing An Equation

Jun 6, 2008

This is a working 12 month intrest equation. I used this for the layout section but I am trying to take this and it gives me the correct values. But what I need to do next is have it sum those values.

I tried =SUM( whole expression but that didnt work) you can laugh at me I know but any help would be great!

=Switch(Fields!eqprecdt.Value< CDate("1 Jan 2007"),Fields!bookvalue.Value*datediff("d",Now(),#1/1/2007#)* .07/365,Fields!eqprecdt.Value> CDate("1 Jan 2007"), Fields!bookvalue.Value * datediff("d",Now(),Fields!eqprecdt.Value)* .07/365)*-1

View 5 Replies View Related

Summing Prices Day By Day

Oct 23, 2013

Let suppose that we have a table which look like this

BillDate Price
01.01.2013 2.00
01.01.2013 1.00
02.01.2013 3.00
02.01.2013 2.00
03.01.2013 1.00

I would like to sum a prices day by day and output to be like this

BillDate SumDaylyPrice
01.01.2013 3.00
02.01.2013 5.00
03.01.2013 1.00

To point I’ve reached myself is a query:

SELECT BillDate, (SELECT SUM( Price) FROM Table1 ) AS SumDaylyPrice
FROM Table1
WHERE BillDate BETWEEN
(SELECT Min(BillDate) FROM Table1)
AND
(SELECT Max(BillDate) FROM Table1)
GROUP BY BillDate

but this doesn’t work- summing everityng

I don’t know how to indicate in first row of query
SELECT BillDate, (SELECT SUM( Price) FROM Table1 WHERE DATE = ????) AS SumPrice
a WHERE clause for every day separately.

View 2 Replies View Related

Summing The Value Of A Text Box.

Feb 5, 2008



As my name shows I am about read to pull my hair out on this and will take any help that I can get.
I have a table with the following values
field1,field2,field3
a | p | 1
a | n | 1
a | p | 2
b | p | 2

b | p | 2
b | n | 3

I am grouping by first column
a
p 1
n 1
p 2
-------------------
a 3 1

b
p 2
p 2
n 3
------------------
b 4 3
What I want to do if it have a value of p I want the value in one column if it has a value of n I want it in another column.
The columns are not a problem, I use a iif statement iif( field2 = p, value, 0) iif ( field2 = n, value, 0)

the problem comes when I try to total the columns.
I was trying to use the =sum(field3) in my group total.
the above example is what I want to see the below example is what I get.
a
p 1
n 1
p 2
-------------------
a 4 4

b
p 2
p 2
n 3
------------------
b 7 7

I hope this makes some since to someone out there that can help me out.
I am getting kind of thin in the hair department so I cannot afford to loose any more.



View 3 Replies View Related

Grouping And Summing

Jul 20, 2006

I need help in summing a column by dates in the format of "YYMMDD". We have multiple orders of the same product each day. I am importing this table to Excel and creating a dashboard. My ultimate goal is to reduce the size of the imported table and still have daily totals of each product. We run thousands of line orders per class which really bogs down Excel. My table in MS Query is as follows (the actual table contains approximately 8,000 lines per month):

date prod class qty
060101 a101 1a 100
060101 a101 1a 100

I would like to have the following:

date prod class qty

060101 a101 1a 200

Any other suggestions would be greatful!!
Thanks in advance

View 4 Replies View Related

Problem Summing Each Column

Jun 4, 2007

 Hi,I need some help in summing each column in a gridview.id    name    sun    mon    tue    wed    total1      Tim       5        6        5      10      263      Sam      6        6        6      5        23The above is how the gridview looks like. In the database, I have all the fields except for total. So, I know I have to use the SUM function in SQl to get the Total. So, I am wondering how do I sum each column to get the total. I have something like this but it doesn't work:"SELECT ID, name, Sun, Mon, Tue, Wed, SUM(Sun + Mon + Tue + Wed) AS Total FROM testTable"Please helpahTan 

View 7 Replies View Related

Summing Different Combinations Of Selections

May 7, 2008

I am quite new in sql. I am writing a report which takes data of one same column and summing them according to the type as described in another column("TR_1"."TTYPE"). So far I have succeeded to get the sum of only one type at a time (by putting WHERE "TR_1"."TTYPE" = or not equal the desired type). For example: I want to create two columns, one showing the sum of the budget and the other the some of the actuals: here is my SQL instruction (the column "TR_1"."TTYPE" give the record type):
******************************************************************
SELECT SUM("TR_1"."AmountLCU")*-1 "Budget",rtrim("TR_1"."COSTCENTER") "Cost Centre",rtrim("TR_1"."ACCOUNT") "Account Num",rtrim("TR_1"."DONOR") "Donor Num", "TR_1"."AmountLCU"*-1 "Amount","TR_1"."TTYPE", rtrim("TR_1"."ACTIVITY") "Activity Code" FROM "scalaDB"."dbo"."A_GL0601_PREVIOUS" "TR_1"
WHERE NOT ("TR_1"."TTYPE"='' OR "TR_1"."TTYPE"='a' OR "TR_1"."TTYPE"='c') AND NOT ("TR_1"."COSTCENTER"=N'' OR "TR_1"."COSTCENTER"=N'0000') AND (("TR_1"."ACCOUNT">=N'26' AND "TR_1"."ACCOUNT"<N'7100') OR ("TR_1"."ACCOUNT">N'7100' AND "TR_1"."ACCOUNT"<=N'7999'))
GROUP BY "TR_1"."COSTCENTER","TR_1"."ACCOUNT","TR_1"."DONOR","TR_1"."ACTIVITY","TR_1"."AmountLCU","TR_1"."TTYPE"

**********************************************************************
Note: the report is written in Crystal reports and the database is SQL Server (not sure of the version)

Thanks in advance
I.Shaame

View 9 Replies View Related

Summing Rows Into A Column

Mar 16, 2006

Hello, This is my first post so please be kind. I have been attempting to convert a query I built in MS Access for use in MSSQL 2000, the syntax for these is different so I was frustrated to find out I could not use the access query.

I have 4 columns one containing a user Id and the others costs, I wish to total the costs per user ID at the end of each row.

So far I have managed to convert about half of my access query, this gives mev the clientID's and costs in columns but I cannot for the life of me get the costs in a total. It's annoying because my access query works perfectly.

This is my Access query:
SELECT DISTINCT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Room_Facilities.FacilityCost, Rooms.CostPerNight,
Rooms!CostPerNight*Nights_Stayed+Holiday_Bookings!Booking_Cost+Room_Facilities!FacilityCost AS TotalCost,
[TotalCost]*17.5/100+[TotalCost] AS [Total+VAT]
FROM Room_Facilities INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID = Rooms.ClientID) ON Hotels.HotelID = Rooms.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID;

And this is what I have been able to salvage into MSSQL format:

SELECT
Holiday_Bookings.ClientID,
Holiday_Bookings.Booking_Cost,
Rooms.CostPerNight,
Room_Facilities.FacilityCost
FROM
Rooms
INNER JOIN Room_Facilities ON (Rooms.FacilityID = Room_Facilities.FacilityID)
INNER JOIN Holiday_Bookings ON (Rooms.Clients_ID = Holiday_Bookings.ClientID)

How can I total the three columns and add the tax?

View 3 Replies View Related

Difficult Summing Query

Nov 23, 2005

Hello,Here is a brief summary:Table 1 = All Accounts- with fields such as Customer ID and Account #Table 2 = Deposit Balance Table- with fields such as Account #, BalanceTable 3 = Loan Balance Table- with fields such as Account #, BalanceAll accounts are either deposit accounts or loan accounts. What I needto do is to gather information about total balances in both depositsand loans for each customer. I haven't been able to hit the right queryfor doing this. I can easily get information about one or the other,such as the following:SELECT All_Accounts.Customer_ID, COUNT (DISTINCT(Deposit_Balance_Table.Account_Number)), Sum(Deposit_Balance_Table.Balance)FROM Product_Table, Deposit_BalanceWHERE (Product_Table.Account_Number=Deposit_Balance.Acco unt_Number)GROUP BY Product_Table.Customer_ID ORDER BY 1Which will give me one row for each user, and show me the total numberof deposit accounts each customer has and a sum of the balances in eachof those accounts. I can make a similar query involving Loan Accounts.As soon as I try to draw both, however, I wind up below my depth.Something to do with the handedness of my joins, I believe. Often Iwill get one column of information (either deposits or loans), or thequery will fail because the join I'm attempting is invalid, etc. I needto take every row in the All_Accounts table, match each one to itsbalance in either the Deposit or Loan table, and then group them all bythe Customer ID and sum them, so that I can find out the totalrelationship balance per customer. Any help would be appreciated.

View 5 Replies View Related

Summing From Multiple Tables

Jul 20, 2005

We recently added a new database at the company. It has only onepurpose - to hold massive amounts a daily data generated by telephonecalls on a network.The amount of data was so large (several gigabytes a day) that the guywho set up the database creates a new table for it each day.His thinking was that if we only need to query one day's worth of datathen it would be a lot faster to query a table with one day's datathan having to query many days of data in one table.I see his reasoning. Any comments or alternatives to this schemewould be appreciated.Here's the question though...I'm writing a front end for this and waswondering if the most efficient way to query and sum data acrossmultiple tables (days) is in the form of the following statement.Suppose three days of worth of data are wanted:select sum(ET) from (select sum(vc_elapsed_time) AS ET fromswitch2030608 where init_cell_info_cell = 196 union all selectsum(vc_elapsed_time) as ET from switch2030609 whereinit_cell_info_cell = 196 union all select sum(vc_elapsed_time) as ETfrom switch2030610 where init_cell_info_cell = 196 ) tIn my front end, based on user input, I plan to keep extending thisstatement with more union alls. Is this the best way to implement thegoal of this query?-David

View 1 Replies View Related

Summing Up Based On Value Of Another Column

May 12, 2008

I have a report with several columns which include Status(0,1,2) and Time in hours. I want to be able to total up the time for each status. I Sum up the all of the fields fine. I'm new to reporting services so any help would be appreciated. Thanks

View 3 Replies View Related







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