I have a column that has an expression with a runningvalue in it, a "Carrying Cost" for each month. I need to create another column that aggregates the monthly Cost. I can't to do a Runningvalue on the Runingvalue. I can't even do a Sum on the Runningvalue.
View 9 RepliesCan any1 tell me why i am getting an error
SELECT DISTINCT
--p.voucher,
--p.amount,
p.siteID,
b.siteID,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS OutStandingBalance,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS CashCheque,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS Vouchers
FROM
BillingTotal b,
Payment p
--WHERE
-- s.sitename=@cmb1
--AND p.siteid = s.siteid
-- p.voucher = 0
-- p.voucher = 1
GROUP BY p.siteID,b.siteID
Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
I have 2 Dimensions in SSAS (see below end), I want to create a calculated member, named
This_Year_Billable_Objective_Count, with its formula = BillableLastYear*(100+ BillableObjective)/100.
The first factor, Â BillableLastYear is a number, aggregated (sum) from child units.
The second factor, Â BillableObjective is a percent number (for example 28 means 28%), it's not aggregate. It's an dependent value for each unit.
How can I calculate This_Year_Billable_Objective_Count for each unit?
\ able 1
SELECT [UnitKey]
   ,[UnitID]
   ,[UnitName]
   ,[Unit2Name]
   ,[Unit3Name]
   ,[Unit4Name]
[Code] .....
How do I aggregate a column by concatenation?
/*
SQL Server 2000 datbase
I have 3 tables.
A "client" table.
A "call history" table.
And a "call detail" table.
The "client" table contains data about individual clients.
The "call history" table contains data about calls to or from the clients in the "client" table. (This is a one-to-many relationship of client-to-history interactions.)
The "call detail" table contains detail data about a particular "call history" entry. (This is also a one-to-many relationship of history-to-detail entries.)
*/
Code:
DECLARE @Client TABLE (ClientID INT PRIMARY KEY, ClientName VARCHAR(100))
INSERT INTO @Client
SELECT 1 [ClientID], 'Client A' [ClientName]
UNION ALL SELECT 2, 'Client B'
UNION ALL SELECT 3, 'Client C'
UNION ALL SELECT 4, 'Client D'
SELECT c.ClientID, c.ClientName
[Code]....
I am trying to get a culmulative Bitwise OR operation on a column by query - rather than using a CLR function.
The problem is shown below:
CREATE TABLE #Operations (
PK INT NOT NULL IDENTITY(1,1),
UserName VARCHAR(50) NOT NULL,
UserProcess VARCHAR(50) NOT NULL,
ServerOperation VARCHAR(50) NOT NULL,
Permission INT NOT NULL );
[Code] ....
So Far I've tried SUM - wrong results, and STUFF - which doesn't seem appropriate for bitwise operation - neither give useful results.
-- SUM Operation - fails
SELECT DISTINCT
UserName,
ServerOperation,
SUM(Permission) AS Permission
FROM #Operations
[Code] ....
It may be possible to materialise the permissions each time one is changed (e.g. by to use a cursor ), however I would prefer a query or function or combination to generate the result.
Hi,
I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.
Thanks
Greetings,
I have a dataset something like this:
Loc Value Time
A 3 1AM
A 8 2AM
A 2 3AM
A 1 4AM
...
B 8 1AM
B 1 2AM
...
When we collapse the Loc group I want to show the Max vlaue and the time it
occurred.
A 8 2AM
B 8 1AM
What is the expression for the Time column of the table to ensure
the time of the maximum value is shown?
Thanks
Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy
View 5 Replies View RelatedI am no expert in sql, but I keep stubbling on this problem:I have a table t1 with 2 columns (a,b)I have a table t2 with 2 columns (c,d)I need to delete all records from t1 which have the same value (a,b)than the value of (c,d) in all records in the t2 table.I oracle, this is simple:delete from t1where (a,b) in (select c,d from t2)because Oracle has support for this syntax. Dont remember how they callit. But this is not support in sql server. So I have to resort to:delete from t1where a + '+' + b in ( select c + '+' + d from t2)Of course, a,b,c,d must be varchar for this to work. Basically I fake aunique key for the records. Is there a better way to do this?Thanks
View 3 Replies View RelatedCan someone please help me with a better way to format the following query. This works, but I know it is hidious.
Code Snippet
select
convert(varchar, processed, 101) as Date,
count(o.id) as [# Orders],
sum(distinct a.runnercount) as [# Runners],
sum(o.total) as [$ Gross],
sum(o.fee) as [$ Fees],
(sum(o.total)-sum(o.fee)) as [$ Net]
from [order] o join (select convert(varchar,processed,101) as date, count(*) as runnercount from orderitem oi inner join [order] o on o.id = oi.orderid where typeofextraid = 4 group by convert(varchar,processed,101)) a on convert(varchar,processed,101) = a.date
where statemented = @statemented
group by convert(varchar, processed, 101)
2 tables: Order and OrderItem. I need the sum of a specific record type from the OrderItem table along with all the other aggregate columns group by day.
Thanks.
Nathan
create a sequential aggregate of a column using sum in tsql.
I want a column that will sum up all hours work by transaction date.
for example
Monday my hours work will be 8 and therefore my total transaction hours will be 8.
Tuesday my hours work will be 8 but I want the total hours of my total transaction hours to 16.
Wed my total hours work will be 5 and I want the total hours of my total transaction hours to 21. etc.
Select myname, weekday, hours worked, Totalhours_ToDate =Sum(hours worked) from table
I need to create a sequential aggregate of a column using sum in tsql.
I want a column that will sum up all hours work by transaction date.
for example
Monday my hours work will be 8 and therefore my total transaction hours will be 8.
Tuesday my hours work will be 8 but I want the total hours of my total transaction hours to 16.
Wed my total hours work will be 5 and I want the total hours of my total transaction hours to 21. etc.
Select myname, weekday, hours worked, Totalhours_ToDate =Sum(hours worked)from table
How can do this. Because my stored function contains same clause except colums name.So I want to use column name as a parameter but how can send column name and use it like Sum(parameter) function .
my procedure like this:
ALTER PROCEDURE [dbo].[ORNEK10]
@YIL VARCHAR(4),
@TEKLIF_TURU VARCHAR(255)
AS
BEGIN
DECLARE @N1 FLOAT
DECLARE @N2 FLOAT
SET @N1 = ( SELECT DEGERI FROM PARAMETRE WHERE PARAMETRE_ADI='N1')
SET @N2 = ( SELECT DEGERI FROM PARAMETRE WHERE PARAMETRE_ADI='N2')
SET NOCOUNT ON;
--I want to avoid using if statements for Sum() function
IF(@TEKLIF_TURU='BASKAN_TEKLIF')
SELECT TOP (100) PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA,
SUM(BASKAN_TEKLIF) AS YILI,
((100+@N1)*SUM(BASKAN_TEKLIF))/100 AS YIL1,
((100+@N1)*(100+@N2)*SUM(BASKAN_TEKLIF))/10000 AS YIL2
FROM GELIR AS G WHERE YIL = @YIL GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP
ORDER BY KOD1, KOD2, KOD3, KOD4
IF(@TEKLIF_TURU='ENCUMEN_TEKLIF')
SELECT TOP (100) PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA,
SUM(ENCUMEN_TEKLIF) AS YILI,
((100+@N1)*SUM(ENCUMEN_TEKLIF))/100 AS YIL1,
((100+@N1)*(100+@N2)*SUM(ENCUMEN_TEKLIF))/10000 AS YIL2
FROM GELIR AS G WHERE YIL = @YIL GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP
ORDER BY KOD1, KOD2, KOD3, KOD4
IF(@TEKLIF_TURU='MECLIS_TEKLIF')
SELECT TOP (100) PERCENT KOD1, KOD2, KOD3, KOD4, dbo.ORNEK10AD(KOD1, KOD2, KOD3, KOD4) AS ACIKLAMA,
SUM(MECLIS_TEKLIF) AS YILI,
((100+@N1)*SUM(MECLIS_TEKLIF))/100 AS YIL1,
((100+@N1)*(100+@N2)*SUM(MECLIS_TEKLIF))/10000 AS YIL2
FROM GELIR AS G WHERE YIL = @YIL GROUP BY KOD1, KOD2, KOD3, KOD4 WITH ROLLUP
ORDER BY KOD1, KOD2, KOD3, KOD4
END
I am trying to create an aggregate table where the value is a rolling sum. Type a on date 1 is the sum of the values in the main tbl. Type a on date 2 is the sum of values for type a on date 1 and date 2. Is this possible? I have been trying update t sql with sum(case where date <= date) statements but can't get it to run.
create table main_table (type nvarchar(10), date int, datavalues int);
insert into main_table
values('a', '1',3);
insert into main_table
values('b', '1',4)
[Code] .....
I have a simple query like:
select max(PTR_DATE)
from MPR_portfolio_transactions
group by PTR_SYMBOL
and this is working fine, but if I add an extra column with another field like:
select max(PTR_DATE) , PTR_SHAREBALANCE
from MPR_portfolio_transactions
group by PTR_SYMBOL
Then I get an error message like:
Column 'MPR_portfolio_transactions.PTR_SHAREBALANCE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
How can I get round to this?
Hello i have this query and I havent been able to solve it
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.FN_NIVELCOMPETENCIACARGO", or the name is ambiguous.
I have tried with dbo and withou dbo, specifyng columns inside it,
Thanks
select a.nmempresa,a.nmtipocompetencia, d.dsnombreciclo, c.dsnombrecargo,a.dstipocompetencia, a.dscatalogo,
b.popeso as PesoTipoCompetencia, f.dsnombrecompetencia as Competencia, e.pocompetencia as PesoCompetencia,
g.DSOPCIONESCALA, g.PESO
from
tgsc_tiposcompetencias a, TGSC_TIPOSCOMPETENCIASxcargo b, tgsc_cargos c, tgsc_ciclos d,
tgsc_competenciasxcargo e, tgsc_competencias f,
(select
dbo.FN_NIVELCOMPETENCIACARGO(a.nmempresa,a.nmciclo, a.nmtipocompetencia,c.nmcargo,f.nmcompetencia)) as g
where a.nmempresa=72 and a.nmciclo=9
and b.nmtipocompetencia=a.nmtipocompetencia
and b.nmcargo=10 and b.nmempresa=72 and b.nmciclo=9
and c.nmcargo=10 and c.nmempresa=72 and c.nmciclo=9 and d.nmciclo=9
and e.nmcargo=10 and e.nmciclo=9 and e.nmempresa=72
and f.nmcompetencia=e.nmcompetencia and f.nmtipocompetencia=a.nmtipocompetencia
and f.nmtipocompetencia=b.nmtipocompetencia
I have a specific variation on the standard 'Column Invalid' question: I have this query that works fine:
SELECT vd.Question ,
csq.Q# ,
csq.Q_Sort ,
csq.Q_SubSort ,
AVG(CAST(vd.Response AS FLOAT)) AS AvgC ,
vd.RType
[Code] ....
When I add this second average column like this:
SELECT vd.Question ,
csq.Q# ,
csq.Q_Sort ,
csq.Q_SubSort ,
AVG(CAST(vd.Response AS FLOAT)) AS AvgC ,
[Code] ....
I get the error: Column 'dbo.vwData.Response' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Clearly things are in the right place before the change, so I can only assume that the OVER clause is my problem. Is this just not possible?
I have a table that has 4 colums (id,projectno,date,price)
i want to make a select that returns the sum per project no
i used this query
select projectno,sum(pice) as sum
from supplier
group by projectno
but i want to include additional columns like id and date for the result
but its giving this message:
Column 'supplier.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
is there a better way to do so without joining the main table with the upper select query?
Best Regards
I need to get the sum of a field that already has an aggregate function (MAX) performed on it. I am using the following query
Code Snippet
SELECT "tI"."ItemID", MAX("vSS"."ShortDesc") "Short Description",
MAX("tPCT"."FreezeQty") "Freeze Qty", SUM("vSS"."QtyOnHand") "Current Qty",
"tPCT"."BatchKey"
FROM ("vSS" "vSS"
INNER JOIN "tI" "tI"
ON "vSS"."ItemKey"="tI"."ItemKey")
LEFT OUTER JOIN "tPCT" "tPCT"
ON "vSS"."ItemKey"="tPCT"."ItemKey"
WHERE "vSS"."ItemID" = '3002954'
GROUP BY "tI"."ItemID", "tPCT"."BatchKey"
It yields the following results
ItemID
Short Description
Freeze Qty
Current Qty
BatchKey
3002954
SET, WRENCH HEX METRIC
-33
129
42221
3002954
SET, WRENCH HEX METRIC
51
129
42244
3002954
SET, WRENCH HEX METRIC
-31
129
42250
I need to SUM the maximum freeze quantity values per item ID. Therefore for this record, I need the following results:
3002954 SET, WRENCH HEX METRIC -13 129
Can this be done via a subquery? Any assistnance would be greatly appreciated?
Thanks,
DLee
Hi,
I have we have a client who gives their invoices in a flat file format, we import it into a SQL Server table.
Nothing is normalized – everything is repeated in every record. The fields are:
customerNumberInvoice_numberPO_numberQtyDescriptionLine_numberLine_totalFreightTaxInvoice_date
So an if an order has 10 line items, the header information (invoice number, PO number, ivoice date) are repeated on each of the lines
I am writing a query to show the following
Order number, Invoice total, Date
select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice_date from invoices group by invoice_no, Invoice, customerNumber
This works great - for each invoice I get the invoice number, InvoiceTotal, and Date
Then I was asked to add the PO Number – this is where I can’t get it right.
When I added “PO_number� to the query, I got two lines for each invoice
select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice, PO_number from invoices group by invoice_no, Invoice, Sold_To_Cust_No, PO_number
Please help - I need to end up with: invoice_no, invoiceTotal, customerNumber, Invoice_date and PO_number (sequence does not matter)
Thanks
I am attempting to wrie a query that will return aggregate totals from two different tables. The problem is that the TotalForecast totals are way to high. How do I write a query to obtain the correct totals?Table 1 - dbo.QM_ResultsColumns - dbo.QM_Results.Special8, dbo.QM_Results.SessionName, dbo.QM_Results.PersonNumberTable 2 - dbo.PM_ForecastViewColumns - dbo.PM_ForecastView.Hierarchy, dbo.PM_ForecastView.ForecastSelect substring(dbo.QM_Results.Special8,0,6) AS Hierarchy, substring(dbo.QM_Results.SessionName,0,11) As CourseCode,count(dbo.QM_Results.PersonNumber) TotalAssociates,sum(dbo.PM_ForecastView.Forecast) TotalForecastFrom dbo.QM_Results INNER JOIN dbo.PM_ForecastView ON dbo.PM_ForecastView.Hierarchy = substring(dbo.QM_Results.Special8,0,6)where SessionMid in ('96882139', '23620891', '45077427', '29721437')AND substring(dbo.QM_Results.Special8,0,6) in ('EZHBA')Group By substring(dbo.QM_Results.Special8,0,6),substring(dbo.QM_Results.SessionName,0,11)Sample of data returned with my current query.Hierarchy CourseCode TotalAssociates TotalForecastEZHBA CARD167200 1179 141480EZHBA CARD167201 1416 169920EZHBA CARD167202 1119 134280EZHBA CARD167204 99 11880Results when I run aggregate query separatelyActual Total takenHierarchy CourseCode TotalTakenEZHBA CARD167200 393EZHBA CARD167201 472EZHBA CARD167202 373EZHBA CARD167204 33Forecasted Total takenHierarchy CourseCode ForecastEZHBA CARD167200 999EZHBA CARD167201 900EZHBA CARD167202 800EZHBA CARD167204 800
View 4 Replies View RelatedDoes anyone know how to make a query and use an aggregate function? This is my current code...any help would be great.
"SELECT tblTopic.Topic_ID, tblTopic.Subject, MAX(tblThread.Message_date) AS MessageDate, tblThread.Message FROM (tblThread INNER JOIN tblTopic ON tblThread.Topic_ID = tblTopic.Topic_ID) WHERE (tblThread.Message_Date LIKE '%' + @fldGenus + '%' GROUP BY tblTopic.Topic_ID, tblTopic.Subject, tblThread.Message">
Also, How can i limit the query to only bringing up 5 records?
I'm trying to get a datagrid to show the 5 most recent forum posts for a particular category.
Thanks.
I have a table that is used for employee evaluations. There are six questions that are scored either 1, 2, 3, 4, or 5. I want to tally the responses on a page, but I wonder if I can do it without 35 separate calls to the database (I also want to get the average response for each question). I know I can do "SELECT COUNT(intWorkQuality) AS Qual1 FROM dbo.Summer_Project_Req WHERE intWorkQuality = '1' " and then "SELECT COUNT(intWorkQuality) AS Qual2 FROM dbo.Summer_Project_Req WHERE intWorkQuality = '2' " and so on. But can I somehow do the aggregating at the page level, and just refer back to a datasource that uses a generic statement like "SELECT intWorkQuality, intDepend, intAnalyze, intWrite, intOral, intCompatibility FROM dbo.Summer_Project_Req"? If I can, I am not sure what type of control would be best to use or what syntax to use to write the code-behind. I would like the results to be displayed in a grid format. Thanks in advance for your help.
View 3 Replies View RelatedI was doing a SUM on my returned rows and i found that what i really want is an aggregate bitwise OR on all the returned rows. Do you know what's the function for that?
Thank you
I have two tables tb1 with item and qtyOnHand and a second table tb2 with item and qtyOrdered I am trying without success to make this happen;select sum (onHand-Ordered) from (select sum (qtyOnHand) from tb1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from tb2 where item = RD35 group by item) as OrderedI kind of gathered it would work based on this http://weblogs.asp.net/jgalloway/archive/2004/05/19/135358.aspxI have also tried this;select tb1.item from (select sum (qtyOnHand) from tb1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from tb2 where item = RD35 group by item) as Ordered, sum (onHand-Ordered) as available from tb1 where tb1.item = RD35Any ides, there are multiple rows of each item in each table tb1 is inventory with several different locations and tb2 is an orders table.
View 3 Replies View RelatedWhat I'm trying to solve:
I have an application that generates SQL queries, and sometimes uses
DISTINCT where the result set has no dupe rows. In terms of database
resources, I'm trying to figure out if it's worth it to change to app to be
smart enough to not use DISTINCT where it won't serve any purpose, or
whether to let it do the DISTINCT and save added complexity to the query
building application. I.e. what is the cost of DISTINCT where there are no
dupe rows?
What I want to know:
Can someone explain how the stream aggregate operator actually goes about
doing its work?
Does this always create a temp table for sorting and discarding duplicates
(for DISTICNT)? If the answer is "no or sometimes", how does it do so in
the case where a temp table is not involved? I noticed the the estimated
I/O for this operator was zero for some queries I wrote agains pubs. Does
this mean that the optimizer believes the temp table needed will fit
in-memory and creates it in-memory? Or does the estimated I/O figure not
included disk writes for work tables?
tia for any info
Bill
Hello,
I was told that on Oracle there's something called an Aggregate Navigator which should be capable of changing the table you're addressing in a query to another table (with aggregate data) and in this way optimize performance in a data warehousing environment.
Is there anything similar in MS SQL Server?
Hi all,
I have a table with the following fields:
carrier,calc_date,ind_id,rcf
I need to run a query to get the following result(by carrier and for each calc_date, calculate the percentage of all individuals who have rcf greater than 0.73):
carrier,calc_date,count of ind with rcf > 0.73, count of all individual, percentage of individuals with rcf's greater than 0.73.
does anyone have an idea of how to achieve that result?
Thanx
MS Access provides FIRST and LAST aggregates to select the first and last values from a group.
These aggregates apparently do not exist in TSQL.
I am after a workaround for the lack of these aggregates.
Any advice would be appreciated.
Hi, I am wanting to query the identifier field via the MAX function.
eg.
need to retrieve the BookID of the greatest Book Price
Code:
SELECT BookID,MAX(BookPrice)
FROM Book
this errors, with:
Column 'BookID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I realise there are other ways such as referencing back on the same table eg.
Code:
SELECT BookID FROM Book WHERE BookPrice = (SELECT MAX(BookPrice) FROM Book)
but was hoping would be a more direct way, is there a specific direct use of MAX for this purpose or another SQL command?
Greetings!
I'm hoping for a little insight for a novice.
Here's what I have:
1 table that looks like this...
Code:
rep_id answer
1 2
1 1
2 1
3 0
3 1
3 1
4 2
What I want is a recordset returned as ... (columns that tally the 1s and 2s respectively with the rep's id#)
Code:
rep_id total1 total2
1 1 1
2 1 0
3 2 0
4 0 1
What I can do so far is get individual recordsets returned for either total1 or total2 but not together.
Here's what I'm using for these...
Code:
SELECT rep_id, COUNT(answer) as total1
FROM myTable
WHERE answer = '1'
GROUP BY rep_id
and
Code:
SELECT rep_id, COUNT(answer) as total2
FROM myTable
WHERE answer = '2'
GROUP BY rep_id
So how do I form a SQL statement that will combine the 2 columns I have created into 1 joined by the rep_id?
To make it a little stickier there may be some nulls in the existing answers but hopefully that is not going to bung me up.
Any help will be thankfully received!!!
Hi,
How can I aggregate a top 5 count across two satellite tables?
e.g. Orders and downloads table each have multiple entries for the same customer ID I would like to count the orders and add them to the downloads count too e.g. 5 orders added to 10 downloads giving 15 as the total for this customer and get a total 'site activity' result which I would like to select the top 5 for.
Any help or pointers would be a great help!
Thanks.
Hello, I need to use last() aggregate function in MS SQL Server 2005 but it is not built in.How to replace this functionality?Thanks.
View 4 Replies View Related