Get First Record From Group By Query

May 30, 2006

Hendra writes "I'd like to know is there any way or any statement that can help me to get first or last record from the group by query ?
I'm using sql Server 2000
Thank's

_Hendra_"

View 1 Replies


ADVERTISEMENT

Getting First Record Of Each Group

May 21, 2002

Is there a way to do this.

If I have a sorting which gives me multiples of column C1 but which can be uniquely identified by C1+C2+C3, and I want to query for the first C1 in each of C1's unique values, how would I go about doing that?

View 1 Replies View Related

Group Min Record

Sep 16, 2004

Hello Guyz,

A small problem here, I have the below table and I need to group and display the record that has the minimum value in the table (this table is derived from a query that permutates some records to give me this result).

F1 F2 F3
QQQ C 2
QQQ B 1
QQQ A 3

expected result:
QQQ B 1

my result:
when I group by F1, First(F2) and MIN(F3):
QQQ C 1

when I group by F1, MIN(F2) and MIN(F3):
QQQ A 1

when I group by F1, F2 and MIN(F3):
QQQ C 2
QQQ B 1
QQQ A 3

Any help would be very much appreciated..


Cyherus

View 1 Replies View Related

Getting Group-by By Record Value In Second Row?

Dec 14, 2014

I have following query

Select Date, Item_Code, SUM(In_Quantity) as In_Quantity, SUM(Issue_Quantity) as Issue_Quantity, (SUM(In_Quantity)-SUM(issue_Quantity)) as BalanceQty from

(
select tbl_add_product.Date as Date, tbl_add_product.Item_Code, tbl_add_product.In_Quantity, 0 as Issue_Quantity from tbl_add_product
where Item_Code = 'pen'
union ALL
select tbl_issue_product.Date as Date, tbl_issue_product.Item_Code, 0 as In_Quantity, Issue_Quantity from tbl_issue_product
where Item_Code = 'pen'
)
X group by Item_Code, Date

which gives following result:

**Date Item_Code In_Quantity Issue_Quanitity BalanceQty**
2014-12-02 pen 100 0 100
2014-12-03 pen 50 50 0

I want to 100 in second row. the logic is that balance Qty from first row should be added to In_Qty so that when Issue_Quantity is subtracted from it, it gives BalanceQty

View 1 Replies View Related

How Can I Get The First Record Of Each Group

Feb 19, 2006

Master,
How can I get the first record of each group which I had selected by "Group by".I try it but failed.3ks

"USE pubs
SELECT top 1 *
FROM titles
GROUP BY type
ORDER BY price"

View 2 Replies View Related

Last Record In Each Group

Feb 28, 2007

Greetings anyone -

I have been attempting to figure out - in Report Builder - how to print only the last record in each group, i.e. the last activity. I see no Last function available anywhere.

Would someone please be so kind as to advise how this can be done?

If this cannot be accomplished in Rpt Builder, it looks as if the Last function is available within Report Designer. Unfortunately, although documentation states it's available, it does not state where this function can be utilized. I'm certainly having no luck.

Help pls?

Tks & B/R

View 1 Replies View Related

SQL Server 2012 :: Obtaining A Comma Delimited List For Each Group In The Output Of A Group By Query?

Jan 10, 2014

I'd like to ask how you would get the OUTPUT below from the TABLE below:

TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B

OUTPUT:

category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6

The code would go something like:

Select category, count(*), .... as id's
from TABLE
group by category

I just need to find that .... part.

View 3 Replies View Related

Select Last Record By Group?

May 18, 2012

query to show last record/Partner or PartnerName.

select 'PURCHASE' as EntityName, d.PartnerName, h.*
from (
select MAX([TimeStamp]) [Data import], COUNT(1) [Numar de inregistrari], StartDate, EndDate, DistributorId
from DataImport.PurchaseHistory
group by DistributorId, StartDate, EndDate
) h
inner join Partner d on d.PartnerId = h.DistributorId
where d.Active = 1
order by DistributorId, StartDate desc, EndDate desc

View 4 Replies View Related

SQL Select Last Record Per Group

Jan 24, 2004

I'm looking for some sql syntax that will return the last entry per group in a secondary table. MEANING: have 2 tables, one with names and the other with visits. I need to be able to display all the patients with there last visits.

TABLE1 info
ID1 fname1 lname1 DOB1
ID2 fname2 lname2 DOB2
ID3 fname3 lname3 DOB3

TABLE2 info
ID1 Visit2
ID1 Visit3
ID1 Visit1
ID1 Visit4
ID2 Visit1
ID2 Visit2
ID3 Visit1

I need a view or SP to return the following:

ID1 fname1 lname1 dob1 visit4
ID2 fname2 lname2 dob2 visit2
ID3 fname3 lname3 dob3 visit1

It seems like it should be a smiple process, only I just can't get the syntax to work.... Any Help would be GREAT!
thx

View 2 Replies View Related

Group By And Latest Record

Aug 8, 2013

Here's what I'm trying to do

TransIDMemberIDFundIDBuyPriceDate
1501101$1042013-01-24
2501102$1012013-04-23
3501102$1182013-02-04
4501102$982013-05-19
5501103$532013-05-21
6501103$392013-07-09

I'm trying to get the latest buy price for each fund a member bought.

So in the above example, I'm trying to formulate a query to retrieve all the latest bought price for memberid 105:

Desire Query Result

TransIDMemberIDFundIDBuyPriceDate
1501101$1042013-01-24
4501102$982013-05-19
6501103$392013-07-09

So far, I've tried using Group By clause on FundID and BuyPrice and MAX() function for the date, but I can' get the TransID.

View 3 Replies View Related

Select One Record From Each Group

Jan 21, 2014

I have for example a table with columns name, surname, id, ..., weight, age. I need to choose from each age group (GROUP BY age) entire record of the person who has the greatest weight. How to construct such a query?

View 1 Replies View Related

Selecting Top Record In A Group?

Mar 18, 2015

I’m writing a document management system. The documents themselves are created from the contents of a database. The database is SQL Server.

The database contains a table, like so:

ClientID, ProjectID, DocumentID, MinorVersion, MajorVersion, Name

Initially, the user will create a “V0.1” document. So the data would look something like

ClientID = 1
ProjectID = 1
DocumentID = 1
MajorVersion = 0
MinorVersion = 1
Name = “My Document”

Thereafter, the user can create new versions as “0.2”, “0.3”, etc., or “1.0”, “1.1”, “2.0”, etc.

For example, a “2.1” document would be stored as:

ClientID = 1
ProjectID = 1
DocumentID = 1
MajorVersion = 2
MinorVersion = 1
Name = “My Document”

The earlier versions will still exist on the database, but the latest version will be 2.1.

There may be several different documents, with different DocumentID’s (e.g. DocumentID = “1”, DocumentID = “2”), etc., and each of these documents may have many versions.

I’m trying to write a query to display a list of documents showing ClientID, ProjectID, DocumentID, MinorVersion, MajorVersion, Name… but the list should only display the latest version of each document.

So, if the database contained the following records:

ClientID, ProjectID, DocumentID, MajorVersion, MinorVersion, Name
1,1,1,0,1,My Document
1,1,1,0,2,My Document
1,1,1,0,3,My Document
1,1,1,1,0,My Document
1,1,1,2,0,My Document
1,1,1,2,1,My Document
1,1,2,0,1,My Second Document
1,1,2,0,2,My Second Document
1,1,2,0,3,My Second Document

My query should return:

ClientID, ProjectID, DocumentID, MajorVersion, MinorVersion, Name
1,1,1,2,1,My Document
1,1,2,0,3,My Document

… where 2.1 is the latest version of Document 1 and 0.3 is the latest version of Document 2.how to do it.

View 4 Replies View Related

SQL Record Selectons By Group

Jun 22, 2007

I have a set of timecard records with a projectNumber, taskNumber and TimeCardHours:

Rec# projectNumbeer taskNumber Employee timeCardHours
1 123 1.01.Test1 John 4
2 123 1.01.Test1 John 6
3 123 1.99.Test3 Mary 8
4 123 1.02.Test5 Mary 4
5 123 1.06.Test6 Mary 4
6 123 1.08.xyz5 Mary 6
7 123 1.03.xyz7 Jane 8
8 123 1.01.xyz4 Jane 5
9 123 1.04.xyz7 Jane 3
10 123 1.05.Test6 Mary 4
11 123 1.07.Test6 Mary 4

I have the following fixed set of tasks:

taskID taskNumberPrefix Task Description phaseID
11.01 Planning1
21.02 Design2
31.03 Requirements2
41.04 Coding3
51.05 Testing4
61.06 Build3
71.07 Packaging3
81.08 Documentation5
91.99 Management1

Finally, I have different phases:

1Planning
2Analysis
3Development
4Testing
5Documentation

What I would like to do is obtain a summary of timecard hours for each phase.

So for example, in Phase 1, Planning, this has two tasks based on PhaseID=1, Planning and Management which have task number prefixes of 1.01 and 1.99 respectively.

What I would like to is sum all the timecard records are LIKE 1.01% or 1.99% so my final results based on the above data would provide the phase name and total hours.

Planning Phase total Hours
Planning 23 (Sum of records 1,2,3,8)
Analysis 12 (Sum of records 4,7)
Coding 8 (Sum of records 5,9,11)
Testing 4 (Sum of record 10)
Documentation 6 (Sum of record 6)

I have tried various select statements, but helps up when I want to have TaskNumberPrefix LIKE taskNumber%.

Thanks for any assistance.

View 4 Replies View Related

Top 1 Record Of A Group Of Records

Aug 14, 2007

Hi all, I am relatively new to sql and have an issue I am trying to solve. I have a table with several records with the same id:

id amount date
-- ------ ------
1 100 01/01/2006
1 2000 06/01/2005
2 200 01/01/2005
2 500 06/01/2007

how would I get the data for the record with the newest date?
So I would return:

id amount date
-- ------ ----------
1 100 01/01/2006
2 500 06/01/2007

Thanks in advance for the help.

View 8 Replies View Related

How To Select Only The First Record From A Group

Sep 18, 2006

I have a question about selecting only the first record in a group. Example: I have table A with primary key = 999. Table B has multiple records with primary key = 999. How can I match Table A 999 with the first occurrence of 999 in Table B, and then extract other field data (such as street address) from the Table B record. I have tried using the Count() function, but it seems that I can only do this using cursors.

Thanks,

BobD

View 2 Replies View Related

Update First Row Of A Group Of Record

Jun 12, 2006

I have a table as shows here:

col1 col2

1 rec1 *

2 rec1

3 rec1

4 rec2 *

5 rec2

6 rec2



and now I need to update the field col1 of the first records with
different col2 value. that means I need to update the col1 filed of the
* marked record.



any suggestion about how to do it with SQL server 2000?



thanks a lot.





View 1 Replies View Related

Query Timeouts When Updating A Record Retrieved Through A Websphere JDBC Datasource - Possible Record Locking Problem

Apr 7, 2008

Hi,

We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.

If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.

Thanks,
Sarah

The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.

This is running on a Websphere Application Server v6.1.

Code snippet - getting the record thru JDBC:


DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();


// wsCon.setAutoCommit(false); //have tried with and without this flag - same results

Statements stmt = wsCon.createStatement();


String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);


if(rs.next()){
System.out.println(rs.getString("lastName"));
}

if (rs != null){
rs.close();
}
if (stmt != null) {

stmt.close();
}
if (wsCon != null) {

wsCon.close();
}

View 1 Replies View Related

T-SQL (SS2K8) :: Display Record Using Group By?

Aug 3, 2015

I would like to display all the products with maximum SeqNo from the table below:

TABLE
ProductIDSeqNoBalance
111215
11135
111420
111510
12115
1212100
121325
121445

OUTPUT

ProductIDSeqNoBalance
111510
121445

View 3 Replies View Related

Retrieve When All Values In Group Have Same Record

Feb 25, 2015

Here is some sample data:

CREATE TABLE
#MyTable
(
Pk INT,
GroupID INT,
Value VARCHAR(10)

[code]...

I am looking to retrieve any GroupID in which every Value of that GroupID is either (a) null, (b) an empty string, or (c) "XYZ". So in the above example, GroupID #1 would not be returned in my query because there is a Value of "ABC", but GroupID #2 would be returned since it consists of only nulls, "XYZ"'s, and empty strings.What would be the most efficient way to write such a query?

View 2 Replies View Related

Select The Last Record Group By? Ident....

May 13, 2007

Hello

I have a table from cars GPS positions:
CREATE TABLE Positions (
Ident VARCHAR(20) NOT NULL,
Valid BOOL,
Date DATETIME NOT NULL,
Latitude FLOAT,
Longitude FLOAT,
Speed INT
}

Sorry about the poor english... :)
I like select the last position where Ident=DEMO1 and DEMO2....
How can I make that?
Practically I sould like to now, where is the last positions of selected cars!
I try this, but not good:
SELECT
MAX(date),
ident,
latitude,
longitude,
speed
FROM
positions
WHERE
valid=1 &&
(ident='DEMO1' || ident='DEMO2'|| ident='DEMO3')
GROUP BY
ident
;

I get 3 record, the dates is the last, but the lat. and long. is wrong...

Thanks,
Laci

View 5 Replies View Related

Get Value Of A Single Record Instead Of Aggregated Value With GROUP BY

Oct 12, 2007

How to get


Code Block

a record value instead of aggregated value with GROUP BY?

Assume that I have a PRODUCT_COMMENT table defined as below. It logs
the multiple comments for products. A product may have multiple
comments logged at different time.





Code Block

CREATE TABLE [dbo].[PRODUCT_COMMENT](
[COMMENT_ID] [int] IDENTITY(1,1) NOT NULL,
[PRODUCT_ID] [int] NOT NULL,
[COMMENT] [nvarchar](50) NULL,
[UPDATED_ON] [datetime] NOT NULL,
CONSTRAINT [PK_PRODUCT_COMMENT] PRIMARY KEY CLUSTERED
(
[COMMENT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[PRODUCT_COMMENT] WITH CHECK ADD CONSTRAINT
[FK_PRODUCT_COMMENT_PRODUCT] FOREIGN KEY([PRODUCT_ID])
REFERENCES [dbo].[PRODUCT] ([PRODUCT_ID])
GO
ALTER TABLE [dbo].[PRODUCT_COMMENT] CHECK CONSTRAINT
[FK_PRODUCT_COMMENT_PRODUCT]

I would like to use the following SQL statement to get the latest
comments for all products.







Code Block

SELECT PRODUCT_ID, COMMENT, UPDATED_ON
FROM PRODUCT_COMMENT
GROUP BY PRODUCT_ID
HAVING UPDATED_ON = MAX(UPDATED_ON)



But this leads to the following error:




Code Block

Column 'PRODUCT_COMMENT.UPDATED_ON' is invalid in the HAVING clause
because it is not contained in either an aggregate function or the
GROUP BY clause.



Is there a way to do that?

Thanks!

View 5 Replies View Related

Return A Record Depend Of Group Result

Apr 12, 2015

I have a table with records like that.

Group | Value
Team 1 | 0
Team 1 | 0
Team 1 | 1
Team 1 | 1
Team 2 | 0
Team 2 | 0
Team 2 | 0

I want a script that return 0 if all the values of the group are 0 and return 1 if the records of the value is mixed with 0 and 1.

View 1 Replies View Related

T-SQL (SS2K8) :: Count Record Based On Group

Dec 10, 2014

This my table named myData

CREATE TABLE [dbo].[myData](
[idx] [int] NULL,
[paymentMethod] [nvarchar](200) NULL,
[daerahKutipan] [int] NULL,
[payer] [int] NULL,

[code]....

I want to calculate the number of payer Group By paymentMethod. The number of payer must be divided by daerahKutipan. So far, my query as follow

select paymentMethod,
COUNT(CASE WHEN daerahKutipan = 1 THEN payer ELSE 0 END) figure_Seremban,
COUNT(CASE WHEN daerahKutipan = 3 THEN payer ELSE 0 END) figure_KualaPilah,
COUNT(CASE WHEN daerahKutipan = 4 THEN payer ELSE 0 END) figure_PortDickson,
COUNT(CASE WHEN daerahKutipan = 5 THEN payer ELSE 0 END) figure_Jelebu,
COUNT(CASE WHEN daerahKutipan = 6 THEN payer ELSE 0 END) figure_Tampin,
COUNT(CASE WHEN daerahKutipan = 7 THEN payer ELSE 0 END) figure_Rembau,

[code]....

View 1 Replies View Related

Find The Last Record By Date In A Sub Group Of Records.

Oct 1, 2007

Looking to see if thier is a better way to find the last record entered in a group of records.

What I'm doing now is finding the max for the secound column and then doing a sub query to find the max of the third column having the second columns equal.

Table example using simplied data.






PolId

CoveragId

EffDate

Status

Limit1


2

1

9/7/2007

a

10000


2

2

9/7/2007

a

150000


2

2

10/1/2007

a

200000


3

1

9/7/2007

a

10000

The parent program addes a row every time the data is changed. To make things worst; the records arn't always in sqenal order like the above table and some time edits the row instead.

The current query returns a single value. from a single table.

Current code used in the select protion on a larger query. bpi = basicpolicyInformation.

( Select c1.limit1
From AFW_Coverage as c1
Where c1.PolId=bpi.PolId
and c1.CoverageId = (select max(CoverageId) as CoverageId
From AFW_Coverage as c
where c.PolId = c1.PolId
and c.CoverageCode = 'Dwelling'
and status <> 'D'
)
and c1.effDate = (select max(Effdate) as Effdate
From AFW_Coverage as c
where c.PolId = c1.PolId
and c.CoverageID = c1.CoverageId
)


Explain the current code. It uses the two sub queries to find the correct record ID that has the data needed.

View 16 Replies View Related

Reporting Services :: SSRS Record Counts For Group Item

Aug 11, 2015

I have a table where I am grouping on one field and would like an individual (separate) count of values from another field of same table.  So for example, I have following data:

instance_id,  area,        values
101              North       1
102              North       2
103             East          2
104             East          2

I would like to report on Area, and count of rows with different Values types, for example:

Area                            Value - 1,    Value - 2,  Value - 3
North                                 1               1              0
East                                   0             2                0

I am not sure what the technical term is for such report, but I can group by Area column, and but its aggregating counts on different Value types that I am having difficulty in performing in SSRS.

View 2 Replies View Related

Reporting Services :: Group And Sum Items / Sub-items Into One Record

Apr 10, 2015

I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).

I have a table of items (i.e. products).  In some cases, items can be components of another item (called "Kits").  In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items".  In our Items table, we have a field called "Parent_Item_Id".  Records for Child Items contain the Item Id of the parent.  So a sample of my database would be the following:

ItemId | Parent_Item_Id | Name | QuantityAvailable
----------------------------------------
1 | NULL | Kit A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
4 | NULL | Kit B | 4
5 | 4 | Item 3 | 21
6 | NULL | Item 4 | 100

Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.

So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item.  So an example of the report would be the following:

Name | Available Qty
--------------------------
Kit A | 15
Kit B | 25
Item 4 | 100

How I can setup my report to group properly?

View 6 Replies View Related

Reporting Services :: Display Group Name Value Of Each Group In Column Header Outside The Group?

Sep 29, 2015

I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.

'GroupName' column has multiple values - X,Y,Z,......

I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...

Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName'  values:

Example:

Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z

ie the column name of ABC (Clm ABC)  must be dynamic as per the GroupName values (X,Y,Z....)

Page1:

GroupName                 Clm ABC-X

X

Page2:

GroupName                 Clm ABC-Y

Y

Page3:

GroupName                 Clm ABC-Z

Z

I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....

However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,

I get the following error:

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope

I need to get the X, Y, Z ... in each page for the column ABC.

I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...

View 4 Replies View Related

SQL Group By Query

Jul 26, 2007

I am back and need some help with Group By query. I am using SQL Server 2000. I have table in a database from which I have to SELECT some records. The first field is callingPartyNumber. I am trying to SELECT a few fields including callingPartyNumber, and SUM(duration). Is it possible to group the results according to the callingPartyNumber field only (knowing the fact that I have other fields also in the SELECT part of the query)?
The type of output I want is that all the records are grouped by the callingPartyNumber field and the sum of duration field for each groups is also returned.
 SELECT callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber,
dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 , '01-01-1970 00:00:00') AS dateTimeConnect,
dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600, '01-01-1970 00:00:00') AS dateTimeDisconnect,
CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration,
SUM(duration)

FROM Calls

GROUP BY callingPartyNumber

I cannot normalize the tables (implies that I have to work on one and only one table).Anyone...
 
 

View 25 Replies View Related

How Do I Group This Query Here

Nov 4, 2007

I have the following sql statement below;DECLARE @val intSET @val = 1WHILE (@val <= 7 )BEGIN SELECT TOP(1) id, queueFROM itn_articles WHERE asection = @valORDER BY queue DESCSET @val = @val + 1END Essential it just loops through a select statement 7 times, now the problem is how would I do this and group my results together so I could ORDER them; instead them coming out in a different query output and, which makes the order unrankable

View 2 Replies View Related

SQL Group By And SUM Query

May 9, 2008

Hi Guys,I have the following select sql (sql server) select statement SELECT
dbo.AlternativesAssessment.AlternativeNumber,
dbo.AlternativesAssessment.UserID,
dbo.AlternativesAssessment.MembershipValue,
dbo.AlternativesAssessment.CriteriaID,
dbo.CriteriaDefinition.CriteriaName,
dbo.CriteriaDefinition.DecisionSessionID

FROM
dbo.AlternativesAssessment INNER JOIN
dbo.CriteriaDefinition ON dbo.AlternativesAssessment.CriteriaID = dbo.CriteriaDefinition.CriteriaID

WHERE (dbo.CriteriaDefinition.SessionID = @SessionID)

ORDER BY dbo.AlternativesAssessment.UserID, dbo.AlternativesAssessment.AlternativeNumber and it produces a result like this:In this case there are 4 alternatives all evaluated through 3 criteria "c1_Cost", "c2_Durability" and "c3_Reputation".My objective is to sum up all the MembershipValue's for each alternative.it shoud look like this:AlternativeNumber|CriteriaName|MembershipValue|    1      Cost           2314    1      Durability        214    1      Reputation        981    2      Cost            814    2      Durability        914    2      Reputation        381.etcDo you guys have an idea how I could achieve that?I played around with SUM's and GROUP By's for hours now but it does not seem to work.Many thanks,polynaux

View 2 Replies View Related

Query Using Group By

Jan 11, 2001

I have a vendor table with vendor codes of six characters. The same vendor could have two codes (I'm new to this accounting system so I don't know why this is the case). If a vendor has two codes, the first character for one vendor code would be an "H", then the first character for the other code would be a "P", with the other 5 characters being the same (i.e. HCAMER and PCAMER).

We want to print only one 1099 for the vendor. So, if the vendor has two codes, I need to get a total for that vendor. Can this be done in a query?

Thanks.

View 1 Replies View Related

Max Query With Group By

Dec 18, 2006

Hi

I have a table in which column are id, date, lname, fname, add
In this table there are duplicate records but have different date for each duplicate record. I am trying to get just max(date) for each record.

when i try like these it works.
select id, max(date) from table group by id

but when i try something like these it does not work
select id, fname, lname, max(date) from table group by id, fname, lname

I get all the record.
I am writing these query from perl script. any help.

View 3 Replies View Related

Group By Query

Jun 22, 2004

Hi,

Please help in writing a SQL query.
I have a table with EmpId,DirectSales,TeamLeaderId,TeamMemSales as some of the columns.The situation is
a Team Manager as sell directly, which comes under directsales and the overriding value of his team member comes under teammemsales.

Now I want get sum of both for a particular manager on a daily basis. Like

SaleDate sum(Directsales) sum(TeammemSales)

if the given id is empid then the it is directsales and if it is in teamleaderid then it is teammemsales.

How to achieve this task in query?

Any help is appreciated.

Thanks

M.L.Srinvas

View 1 Replies View Related







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