Aggregate Bitwise OR On A Column

Oct 22, 2014

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.

View 5 Replies


ADVERTISEMENT

Aggregate Bitwise OR

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

SQL 2012 :: Custom Bitwise OR Aggregate Function

Mar 18, 2014

I want to create a custom bitwise OR aggregate function.

I want to use it like the built in aggregate functions (MIN, MAX etc.)

SELECT dbo.bitwise_or(bit_string) FROM table

where bit_string is a nvarchar(3)

so for example if the table contains two rows ('100', '001') the above query should return '101'

I would like to implement this as a CLR function/assembly with the aggregate below:

CREATE AGGREGATE dbo.bitwise_or (bit_string nvarchar(3))
RETURNS [nvarchar(3)]
EXTERNAL NAME [Aggregate].[bitwise_or]

I have followed this post to implement amedian aggregate function [URL] ..... but there is a lot of code (not sure what is really needed in my case).

View 9 Replies View Related

How To Aggregate A Column That Was Itself An Aggregate

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

Query To Find A Value In Column B Based On An Aggregate Function On Column A?

Jul 20, 2005

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 Related

How To Aggregate A Column By Concatenation

Mar 28, 2012

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]....

View 5 Replies View Related

Aggregate (SUM) A Column And Then Use The Result?

May 7, 2007

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

View 9 Replies View Related

Selecting The Corresponding Column Value When Using Min Or Max Aggregate.

May 4, 2007

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

View 8 Replies View Related

Aggregate Column Comparison In Sql Server Sql ?

Jul 27, 2005

I 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 Related

How To Get Aggregate Column From Joined Table

Feb 6, 2008

Can 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

View 1 Replies View Related

T-SQL (SS2K8) :: Create Sequential Aggregate Of A Column Using Sum?

Aug 17, 2015

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

View 5 Replies View Related

Transact SQL :: Create Sequential Aggregate Of A Column Using Sum?

Aug 17, 2015

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

View 3 Replies View Related

Sending Column Name As A Parameter To Aggregate Function

Jul 29, 2006

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

View 4 Replies View Related

SQL Server 2012 :: Updated Aggregate Column Where Value Is A Rolling Sum

Apr 29, 2014

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] .....

View 9 Replies View Related

Aggregate Functions - Adding Extra Column With Another Field

Jun 13, 2014

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?

View 6 Replies View Related

Bitwise Help!

Nov 27, 2007

Hi,

This is a crosspost, I also asked this question in the learnvisualstudio.net forum. I'm trying to engineer a bit of code in SQL that "decodes" a value stored in a table that represents an array of boolean values, stored using bitwise operations. I have the number, a reference of bit positions for each item, and the actual items checked per the program that normally processes this value. I'm trying to write a sproc that will perform actions based on the booleans in my list, but I can't get the same results the interface gives me.
Here's an example:
Value is 66756
Bit positions 2, 6, 7, and 10 should all return true. I was trying to deduce this using the following:




Code BlockSELECT CONVERT (BIT, <value> & (2 ^ <position>))





but that doesn't work at all. It's my (broken) translation of the code normally used to display this value as a checkbox, which comes from VB6 and looks something like this:




Code Block.ListItems(CStr() & "_").Checked = CBool <value> And (2 ^ <position>))





Can anyone tell me what I'm doing wrong? Thanks in advance.

View 3 Replies View Related

Bitwise NOT Operation

Sep 6, 2006

I was looking into some of the System stored procedures like sp_denylogin, sp_grantLogin etc. I found bit wise operations like

update master.dbo.sysxlogins set xstatus = (xstatus & ~1) | 2, xdate2 = getdate()
where name = @loginame and srvid IS NULL

How does the bitwise NOT(~) works with different datatypes like INT, SMALLINT and TINYINT?

Look at the following sample code and result. I just wanted to understand how it works with int and smallint


declare @a int
declare @b smallint
declare @c tinyint

select @a = 1, @b = 1, @c = 1

select @a=~@a, @b= ~@b, @c= ~@c

select @a, @b, @c

Result
~~~~~
-2-2254

Thanks in advance
Gnana

View 3 Replies View Related

Non-bitwise Exclusive Or?

Jan 30, 2004

Is there a way to do a logical exclusive OR (XOR) in sql server?

I'm trying to do this in where clause, something like:

WHERE
(not exists (select 1 from table a where a.date > '01/30/03') XOR
exists (select 1 from table a where a.date < '01/30/03'))

Thanks!

View 14 Replies View Related

Bitwise Question -

Jul 23, 2005

I am new to bitwise thing in MSSQL.Let's suppose there's a table of favorite foodsinsert int fav_foods(food_name,bitwiseVal)values('Pasta',1)insert int fav_foods(food_name,bitwiseVal)values('Chicken',2)insert int fav_foods(food_name,bitwiseVal)values('Beef',4)insert int fav_foods(food_name,bitwiseVal)values('Fish',8)insert int fav_foods(food_name,bitwiseVal)values('Pork',16)How do I write query to find people who selected more than one item andselected items from "Pasta, Chicken, Beef, Pork"(but not fish)?I hope my question is not confusing.....

View 6 Replies View Related

Cannot Find Either Column Dbo Or The User-defined Function Or Aggregate Dbo.FN_NIVELCOMPETENCIACARGO, Or The Name Is Ambiguo

May 4, 2006

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

View 7 Replies View Related

Decoding Days Bitwise AND

Nov 11, 2004

Hello All

I'm working on a recurring multi-day appointment program. Basically the user can choose a meeting on multiple days of the week over a span of time. For example: Tuesday and Thursday from 10:00 to 10:30 from December 1st 2004 to February 27th 2005.

So I've decided the best way to handle this is to assign a value to each day of the week like so:
MON = 1
TUE = 2
WED = 4
THU = 8
FRI = 16
SAT = 32
SUN = 64

So if the user picks TUE and THU that would be 2 + 8 = 10. The value is unique and seems to work.

So the values would be:
@begin_date = '12/01/2004'
@begin_time = '10:00 AM'
@end_date = '02/27/2005'
@end_time = '10:30 AM'
@recur_days = 10

Now I want to pass the values to stored procedure that will decode the recur_days variable and create entries in a table for each date. I'm struggling to figure out 2 things

1. How do I decode the 10 back into 2(TUE) + 8(THU) ( I think it has something to do with the bitwise AND "&" operator but I'm not sure how to use it.)

2. What is the best way to loop through the date range and create a record for each day?

Regards
Russ

View 2 Replies View Related

Bitwise &&amp; Operator Does Not Work

Jan 15, 2007

I would like to do something like a SELECT * FROM Files WHERE (Attributes & ?) but the & operator isn't recognized. I looked at the documentation sample for it and in the sample the & operator isn't used in the WHERE clause so I thought that might be the reason but I still can't get the operator to work. So even the doc sample code for it doesn't work.

View 3 Replies View Related

SQL Server 2012 :: Column X Is Invalid In Select List Because It Is Not Contained In Aggregate Function

Apr 3, 2015

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?

View 1 Replies View Related

Cannot Perform An Aggregate Function On An Expression Containing An Aggregate Or A Subquery.

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

Analysis :: Calculated Member From Aggregate And Non-aggregate Factors

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

Query Trouble Regarding Bitwise Exclusive

Feb 25, 2001

HI, i am trying to make query that has computations with it. but when there's a point computing between int and float i had to use cast() function to convert certain data types. somehow it only works on converting float to integer because when i'm converting an integer into float inorder to be computed with a float it bombs. my query is like this ....


SELECT cast(((cast(((lat - (SELECT LAT FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210)) * 69.1) AS int) ^ 2) + (cast((69.1 * (lng - (SELECT Lng FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210)) * (COS((SELECT LAT FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210) / 57.3))) AS int) ^ 2)) AS float) ^ .5
FROM TPS_ZIPUSA


.5 is where the query bombs. any idea why is this happenning?

by the way, i'm using sql server 7.0.

francis,

View 2 Replies View Related

Escaping Bitwise In Full Text Search 2005

Feb 6, 2007

Hi,

We are facing a problem while passing a string containing the "&" character into Full Text search CONTAINS predicate. The records that do have this character are not being returned by the search.

I'd like to raise two questions:

1) Is there any special way to escape this character?
2) Does FTS index it at all?

We have tried all known (to us) ways of escaping like doubling the character, using char(38), using ESCAPE etc..Nothing seem to work. Any help would be appreciated.

Thanks,
Alex

View 2 Replies View Related

Using Aggregate

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

Aggregate On An Aggregate

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

Help With Aggregate Query - Can't Get It Right!!

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

Aggregate Query Help

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

Aggregate Function

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

Aggregate Functions

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







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