SQL Mixing Aggregate && Non-aggr Exprs
Jan 7, 2004
How do I create a query that emulates a mix of aggregate & ‘non-aggregate’ expressions. I am using the query as the rowsource for a list box in Access.
Here is my query:
strSql = "SELECT tblTestHeader.TestHdrUniq, tblTestHeader.TestDate, " _
& "'" & Forms("frmCompanySearch").[lstCompanySrch].Column(2) & "'" _
& " + ' ' + tblLoadCell.Abbrev + ' ' + tblTestHeader.CertNumSequence AS CertNum, " _
& "tblLoadCell.Description AS LoadCell, " _
& "tblTestType.Description AS Test, " _
& "tblTester.Sname + ', ' + tblTester.Gname AS Tester " _
& "FROM tblTestHeader INNER JOIN tblLoadCell ON " _
& "tblTestHeader.LoadCellUniq = tblLoadCell.LoadCellUniq INNER JOIN " _
& "tblTester ON " _
& "tblTestHeader.TesterUniq = tblTester.TesterUniq INNER JOIN " _
& "tblTestType ON " _
& "tblTestHeader.TestTypeUniq = tblTestType.TestTypeUniq " _
& "WHERE CompanyUniq = " _
& Forms("frmCompanySearch").[lstCompanySrch].Column(1) _
& " ORDER BY tblTestHeader.TestDate, CertNum"
I want to include another column:
max(tblTestDetail.CertChar) as LastChar
but must have all or no aggregate expr. What is work around for this ?
View 1 Replies
ADVERTISEMENT
Jul 23, 2005
Hi,What I want is to to get SUM of col1 and list quarter data whenapplicable.DDL:create table #temp (col1 int, rent int, transport int, qtr smallint,other int);DML:insert into #tempvalues(1, 800, 300, 1, 200)insert into #tempvalues(1, 800, 300, 2, 300)insert into #tempvalues(2, 800, 300, 2, 400)Data retrieval DML:select col1, sum(other) as other_Total, case when qtr = 1 thensum(other) end qtr_totalfrom #tempgroup by col1,qtrCurrent Resultset:col1 other_Total qtr_total----------- ----------- -----------1 200 2001 300 NULL2 400 NULLDesirable Resultset: (get ride of the middle row above and add up the200 and 300), so, it would look likecol1 other_Total qtr_total----------- ----------- -----------1 500 2002 400 NULLWhat am I missing here?TIA.
View 2 Replies
View Related
Nov 28, 2007
Hi I have this
select emailid,
count(emailid) as 'No.of occurences',
FROM tableA
where start_moment between '2007-11-01' and '2007-11-02'
GROUP BY emailid
having (COUNT(emailid) > 1)
Fair enough this returns the emailid along with the amount of times it appears (all greater then 1 .. duplicated in other words)
My question is , there is also a start_moment field in tableA
so i need to get the max or min start_moment along with the above result?
View 3 Replies
View Related
Oct 30, 2007
My sales are broken into two different major categories: retail and internet. Rather than create two identical fact tables (ala AdventureworksDW) I created on fact table with a dimension that slices by retail or interent. I use that dimension as a parameter on nearly every one of my reports. The use can get retail, internet or both. So far, so good.
Now I need to create a report that shows retail sales and internet sales in adjacent columns on the same report. Is there a way to filter individual columns in a table? Or will I have to rethink how I store my data in the cube?
Thanks.
View 3 Replies
View Related
Sep 26, 2006
I've encountered this problem multiple times in the past and I have a solution but wonder if there might be a more elegant method of achieving the same result...
Take the following example:SELECT * FROM [User]
LEFT OUTER JOIN [Profile] ON [Profile].[UserId] = [User].[UserId]
INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]
Users may or may not have a profile (but never more than one). A profile may or may not have a department (but never more than one).
Now, this will return only users that have a profile even though an outer join has been used. What I really want is to return all users and include their profile and department details but only when the profile has a department.
The solution I have used in the past is:
SELECT * FROM [User]
LEFT OUTER JOIN
(
SELECT *
FROM [Profile]
INNER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]
) [ProfileDepartment] ON [ProfileDepartment].[UserId] = [User].[UserId]
The trouble here is that I've lost the ability to reference department and profile independantly in the outer query. Also, more complex scenarios can also become horribly complex if this needs to be done multiple times in the same query.
I could do this:SELECT * FROM [User]
LEFT OUTER JOIN [Profile] ON [Profile].[UserId] = [User].[UserId] AND [Profile].[DepartmentId] IS NOT NULL
LEFT OUTER JOIN [Department] ON [Department].[DepartmentId] = [Profile].[DepartmentId]
But again I feel that the intention is not at all clear. I want to inner join department to profile because I'm only interested in profiles with a department and departments referenced by a profile.
I would like to be able to specify that the departments should be inner joined to profiles and whichever profiles remain get outer joined to users whilst retaining department and profile as seperate entities within the query.
Is there any way to use brackets to indicate an order of precedance to the logical joins within the from clause?
Daniel
View 3 Replies
View Related
Oct 19, 2007
Can 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.
View 8 Replies
View Related
Sep 22, 2015
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] .....
View 6 Replies
View Related
Jan 26, 2008
I want to create a filtered view according to some information provides.
for example I want to create in form 2 check boxes male and female and when either both or one of them is unchecked, the query filters out the unchecked content from view according to the other filtering conditions.
the exact thing I need is:
a text box for string looking (in a specific column).
male & female checkboxes.
two datetime textboxes to specify a range to focus in.
it is very important to me, so if you misunderstood my question, please contact me.
View 2 Replies
View Related
Jul 24, 2007
Hello, how do you setup a Profiler filter, that has mixed AND/OR operators. Thru the Profiler GUI, I don't even see how you would ask for any statement/proc with READS over 10,000 OR DURATION over 1000ms. How do you do an "OR" thru the GUI?
Forgetting the GUI and doing traces thru scripts, the @logical_operator isn't too logical, and I can't find any description for setting this parameter. If you only have one filter, it doesn't matter if you set it to 0 (AND) or to 1 (OR)... But if you want multiple filters, so one filter is Reads >= 10,000 and the other filter is Duration >= 1000ms., how do you do that? I tried it with the @logical_operator of "1" on both, but then I also got other events with a NULL value in READS.
Code Snippet
-- @traceID = 1
-- @columnID = 13 (Duration)
-- @logicalOperator = 1 (OR)
-- @comparison_operator = 4 (greater or equal)
-- @value = 1000000 (1000 ms)
sp_trace_setfilter 1, 13, 1, 4, 1000000
-- @traceID = 1
-- @columnID = 16 (Reads)
-- @logicalOperator = 1 (OR)
-- @comparison_operator = 4 (greater or equal)
-- @value = 10000 (# Reads)
sp_trace_setfilter 1, 16, 1, 4, 10000
Looks like DURATION is never NULL for these events>>>
RPC:Completed
SP:Completed
SPtmtCompleted
SQL:BatchCompleted
SQLtmtCompleted
but the READS value cane be null (for SP:Completed at least).
Any ideas on how to mix AND/OR operators in multiple filters of one trace?
Thanks, Bruce
View 1 Replies
View Related
Dec 14, 2007
I have an update command with 7 parameters, but at run time the order to the parameters gets mixed up.
I'm using a stored procedure. At first I have the command type set to text, and was calling it using EXEC spName ?,?,?,?,?,?,?
I then named each of the parameters and set their sources. The parameters are like this (samepl name, then source, then type):
A : QueryString - intB: Control - intC: Control - intD: None - intE: None - decimalF: Control - datetimeG: Control - datetime
At run time I was getting an error that an integer couldn't be converted to date time. So I put a breakpoint in the Updating event and then looked at the parameters prior to update.
This is how they looked (Parameter index, paramter name):
[0] A[1] B[2] D[3] E[4] F[5] G[6] C
It didn't maek any sense. Do, I deleted all of the paramters and readded them. That didnt' work. Then I changed the command to StoredProcedure and refreshed the parameters from the stored proc and it brought them in the right order, but the problem remains the same.
I looked at the page source, and there are no indexes in the page source, but the parameters are listed in the proper creation order, as follows:<UpdateParameters><asp:QueryStringParameter Type="Int32" Name="PROJ_ID" QueryStringField="pid"></asp:QueryStringParameter><asp:ControlParameter PropertyName="SelectedValue" Type="Int32" Name="TASK_UID" ControlID="fvTask"></asp:ControlParameter><asp:ControlParameter PropertyName="SelectedValue" Type="Int32" Name="ASSN_UID" ControlID="gvResources"></asp:ControlParameter><asp:Parameter Type="Int32" Name="RES_UID"></asp:Parameter><asp:Parameter Type="Double" Name="Work"></asp:Parameter><asp:ControlParameter PropertyName="Text" Type="DateTime" Name="Start" ControlID="TASK_START_DATETextBox"></asp:ControlParameter><asp:ControlParameter PropertyName="Text" Type="DateTime" Name="Finish" ControlID="TASK_FINISH_DATETextBox"></asp:ControlParameter></UpdateParameters>
No mater what I do, at run time ASSN_UID is always the last parameter. I've also run a SQL trace to see how it is actually being executed, and sure enough, its passing the value for ASSN_UID as the last parameter, which obviously doesn't work.
Any ideas as to why this would happen or how to fix it?
(I guess I can reorder the patameters in the stored proc to match how they are being passed, but still, that wouldn't be a very comfortable solution, since it could perhaps revert at some point or something)
View 2 Replies
View Related
Jan 26, 2008
I'm trying to populate a table of pending emails. The problem is I need
to populate the email field using a select statement but the message
field with static text. Can this be done or is another approach more
prudent? What I have is below but is kicking errors:DECLARE @msg varchar(300) SET @msg = 'New users have applied for accounts. Please review their information.'IF @Type='CreateUserApply' INSERT INTO cdds_Email (Address,Message)VALUES (SELECT M.EmailFROMdbo.aspnet_Membership MINNER JOINdbo.aspnet_UsersInRoles UINNER JOINdbo.aspnet_Roles RON U.RoleId = R.RoleIdON U.UserId = M.UserIdWHERER.RoleName = 'Manager',@msg)
View 3 Replies
View Related
Feb 5, 2006
Hi all,
As part of the logging process for data input, I want to update two fields in a logging table. The first is a datetime, derived from looking up the maximum value in another table (the table I've just imported), and the second is an integer - the number of rows captured in a variable during the task.
I can do this in two separate Execute SQL tasks as follows:
Task 1 syntax
DECLARE @maxDate datetime
SELECT @maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW
UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= -1
WHERE SourceTableName = 'cancel'
Task 2 Syntax, with the variable user::rowsimported mapped to parameter 0
UPDATE dbo.tblLogging
SET
RowsReturned= ?
WHERE SourceTableName = 'cancel'
However I cannot make this work with a single SQL statement such as
DECLARE @maxDate datetime
SELECT @maxDate = max(dtLastChangedDate)
FROM dbo.tblCancel_RAW
UPDATE dbo.tblLogging
SET PreviousFilterValue = CurrentFilterValue,
CurrentFilterValue = ISNULL(CAST ( @maxdate as varchar(25)),CurrentFilterValue),
DateSourceTableLastRead = GetDate(),
RowsReturned= ?
WHERE SourceTableName = 'cancel'
because no matter how I try to map the parameter (0,1,2,3,4 etc) the task fails.
Is this behaviour by design, is it a bug, or is there something I've missed?
Thanks as ever,
Richard
View 1 Replies
View Related
Oct 10, 2015
I have two different roles, each one with a dax filter. One is for filtering users that access by Excel, and other for filtering users that access by Reporting Services, respectively:
=CONTAINS(FILTER(V_FILTRO_C;V_FILTRO_C[IDPerfilGrupo]<>7);V_FILTRO_C[iCursoCod];[SK_CURSO];V_D05_DIM_UTIL[DSC_LOGIN_USER];RIGHT(USERNAME();LEN(USERNAME())-SEARCH("";USERNAME())))
=CONTAINS(FILTER(V_FILTRO_C;V_FILTRO_C[IDPerfilGrupo]<>7);V_FILTRO_C[iCursoCod];[SK_CURSO];V_FILTRO_C[IDUtilizador];VALUE(CUSTOMDATA()))
To create only one role that serves Excel and Reporting Services users, is it viable to use only the || (OR) operator?, is there any other regard i should take?
View 2 Replies
View Related
Jul 15, 2005
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 Replies
View Related
Mar 24, 2007
I have a Stored Procedure for processing a Bill of Material.
One column on the Assembly Table is a Function Name that contains some busniess rules.
OK, now I'm doing a Proof of Concept and I'm stumped.
Huuuuh!
I will ultimately have about 100 of these things. My plan was using Dynamic SQL to go execute the function.
Note: The function just returns a bit.
So; here's what I had in mind ...
if isnull(@FnNameYN,'') <> ''
exec spinb_CheckYN @FnNameYN, @InvLineID, @FnBit = @FnBit output
CREATE PROCEDURE dbo.spinb_CheckYN
@FnNameYN varchar(50),
@InvLineID int,
@FnBit bit output
AS
declare @SQL varchar(8000)
set @SQL = '
if dbo.' + @FnNameYN + ' (' + convert(varchar(31),@InvLineID) + ')) = 1
set @FnBit = 1
else
set @FnBit = 0'
exec (@SQL)
GO
Obviously; @FnBit is not defined in @SQL so that execution will not work.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@FnBit'.
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@FnBit'.
So; is there a way to get a value out of a Dynamic SQL piece of code and get that value INTO my OUTPUT variable?
My many thanks to anyone who can solve this riddle for me.
Thank You!
Sigh: For now, it looks like I'll have a huge string of "IF" statements for each business rule function, as follows:
Hopefully a better solution comes to light.
------ Vertical Build1 - Std Vanes -----------
if @FnNameYN = 'fnb_YN_B1_14'
BEGIN
if dbo.fnb_YN_B1_14 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
------ Vertical Build1 - Scissor Vanes -----------
if @FnNameYN = 'fnb_YN_B1_15'
BEGIN
if dbo.fnb_YN_B1_15 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
.
.
.
etc.
View 10 Replies
View Related
Mar 24, 2008
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
View 6 Replies
View Related
Sep 25, 2007
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
View 6 Replies
View Related
Jun 17, 2007
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
View 4 Replies
View Related
Dec 21, 2007
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 Related
Feb 21, 2008
Does 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.
View 2 Replies
View Related
May 14, 2008
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 Related
Dec 4, 2003
I 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
View 4 Replies
View Related
Jul 11, 2005
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 Related
Jul 24, 2001
What 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
View 1 Replies
View Related
Jan 20, 2000
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?
View 1 Replies
View Related
Jun 6, 2003
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
View 4 Replies
View Related
Jun 26, 2002
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.
View 3 Replies
View Related
Jan 26, 2008
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?
View 1 Replies
View Related
Jun 15, 2006
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!!!
View 2 Replies
View Related
Aug 17, 2006
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.
View 3 Replies
View Related
Mar 9, 2007
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
Apr 3, 2007
I have three tables, tblschedule, tblresource and tblemployeename.
in tblschedule table there are scheduleID, resourceID and employeeID. In tblResource there are ResourceID and ResourceName. In tblemployeename there are EmployeeID, EmployeeFName and EmployeeLame. I want to have a report that show how many times the resource has been reserved by employee. i would like to have a report. Look like the following:
ResourceName EmployeeFName EmployeeLName (Or use EmployeeName) Number of record.
How to write a query? I use asp.net as front end.
Thank for your help.
View 1 Replies
View Related
Jun 11, 2007
I need to find an aggregate for several fields in a row
e.g. Max(date1, date2, ..., dateN)
I can pass this to a delimited string,
pass the string to an UDF that returns a table
and run Max(tablefield) on that UDF
Unfortunately I can only get this working for 1 delimited string at a time
Ideally I would want to include the function in a SELECT statement, e.g. something like
SELECT t1.a, dbo.MaxOfFieldValues(t1.d1+','+t1.d2+...+','+t2.dN )
FROM t1
I got it working with the following two udfs, but I am sure visitors here have solved this a bit smarter:
ALTER Function [dbo].[MaxOfFieldValues]
(
@ListOfValues varchar(8000)
, @delimiter varchar(10) = ','
)
RETURNS VARCHAR(8000)
AS
BEGIN
--Need to get the maximum changedate first
--pass the fields as one value (a delimited string)
--and calc the max
declare @result varchar(8000)
declare @remainder varchar(8000)
set @remainder = @ListOfValues
declare @NoOfItems int --items = delimiters +1
SET @NoOfItems = (len(@ListOfValues) - Len(Replace(@ListOfValues,@delimiter,''))/Len(@ListOfValues))+1
declare @counter int
set @counter =1
set @result = dbo.TakePart(@remainder,@delimiter,@counter)
WHILE @counter <= @NoOfItems
BEGIN
set @counter = @counter + 1
IF @result < dbo.TakePart(@remainder,@delimiter,@counter)
BEGIN
SET @result = dbo.TakePart(@remainder,@delimiter,@counter)
END
END
RETURN (@result)
END
ALTER FUNCTION [dbo].[TakePart]
(
@param varchar(8000)
, @delimiter varchar(10)
, @NumPart int
)
RETURNS varchar(8000)
AS
BEGIN
--Note: maybe smarter to whack the delimiter to the end of the string to avoid the IF statement
declare @result varchar(8000)
declare @remainder varchar(8000)
declare @counter int
set @result = ''
set @remainder = @param
set @counter = 1
WHILE @counter < @Numpart
BEGIN
SET @remainder = SUBSTRING(@remainder,CHARINDEX(@delimiter,@remaind er,1)+Len(@delimiter),8000)
SET @counter = @counter +1
END
IF @counter > (len(@param) - Len(Replace(@param,@delimiter,''))/Len(@delimiter))
BEGIN
SET @result = @remainder
END
ELSE
BEGIN
SET @result = LEFT(@remainder,CHARINDEX(@delimiter,@remainder,1) -1)
END
RETURN @result
END
Cheers
Drio
View 5 Replies
View Related
