Age Old Question About GROUP BY Clause (i Think) - Probably Easy Answer

Jul 20, 2005

How does one get the primary key of the row that is joined in via a
group by aggregate clause when the aggregate is not performed on the
primary key?

For example,

Person table
(
PersonID int,
FirstName varchar(50)
LastName varchar(50)
)


Visit table
(
VisitID int,
PersonID int,
VisitDate datetime
)

These are simplified versions of my tables. I'm trying to create a
view that gets the first time each person Visited:

selectp.PersonID,
min(v.VisitDate)
fromVisit v
joinPerson p on p.PersonID = v.PersonID
group byp.PersonID

The problem is that I would like to return the VisitID in the
resultset, but when I do it expands the query since I have to also put
it in the group by clause.

What are the different ways to achieve this?
Subqueries?
Only return the date and then join off of date on the outside?

Neither of these seem too entising...

Thanks in advance for any help.

-Dave

View 6 Replies


ADVERTISEMENT

Easy SQL Question....first To Answer Correctly Gets....

Aug 31, 2000

Hi Folks,

Easy SQL question....first to answer correctly gets...the satisfaction of knowing he helped a fellow developer in need.

Runnning SQL 7.0 on an NT 4.0 platform in a client server network environment. Authentication is done on the NT level. Each user has his/her own name and password. Most users use the same shared software app.

QUESTION: Can the same user login to multiple machines concurrently and run applications without fear of locking or hanging or causing other mischief?

TIA.

JWB

View 3 Replies View Related

Query Help-----prob An Easy Answer Out There Somewhere

Sep 21, 2007

I am curretnly a newbie at SQL Server..but i am really good with Access.....i am looking into converting to SQL Express but i have this one issue. Below is a snippet of SQL from my VB6 app. SQL Server 2005 says i cant use 'Cdbl'

T, W, L_value.....are strings in the varchar in my SQL DB. OS_T, L, W dont exist on my actual DB i make the RS disconnected and play with the data. This snippet works fine in Access, but as expected it doesnt in SQLS...Wha are my options, if any? Thanks in advance for any help or advice.



strSQL = strSQL & "ORDER.OS_T_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_T_Sort, "
strSQL = strSQL & "ORDER.OS_W_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_W_Sort, "
strSQL = strSQL & "ORDER.OS_L_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_L_Sort, "

View 10 Replies View Related

Query Help-----prob An Easy Answer Out There Somewhere

Sep 21, 2007

I am curretnly a newbie at SQL Server..but i am really good with Access.....i am looking into converting to SQL Express but i have this one issue. Below is a snippet of SQL from my VB6 app. SQL Server 2005 says i cant use 'Cdbl'
T, W, L_value.....are strings in the varchar in my SQL DB. OS_T, L, W dont exist on my actual DB i make the RS disconnected and play with the data. This snippet works fine in Access, but as expected it doesnt in SQLS...Wha are my options, if any? Thanks in advance for any help or advice.

strSQL = strSQL & "ORDER.OS_T_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_T_Sort, "
strSQL = strSQL & "ORDER.OS_W_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_W_Sort, "
strSQL = strSQL & "ORDER.OS_L_value, "
strSQL = strSQL & "Cdbl(0.0) as OS_L_Sort, "

View 8 Replies View Related

Quick Question, Easy Correct Answer, Thanks!

Mar 13, 2008

the first line works, but the 2nd line doesn't.  I need just the CompanyKey as the table name. Help appreciated.
sqlcreate = "CREATE TABLE Company_" + CompanyKey + " (UserID VARCHAR(50), First_Name VARCHAR(50), Last_Name VARCHAR(50), Title VARCHAR(50), Division VARCHAR(50))"
sqlcreate = "CREATE TABLE " + CompanyKey + " (UserID VARCHAR(50), First_Name VARCHAR(50), Last_Name VARCHAR(50), Title VARCHAR(50), Division VARCHAR(50))"

View 10 Replies View Related

GROUP BY, CASE (easy) Problem

Nov 16, 2006

I am not to experienced with SQL and I guess the following is a piece of cake for most of you...
I want to get the sales amount for each shop, for each of three different products, and for the sum of the three products. The problem is that I only get for each shop for each of the product, but not the sum of the three products OR for each shop the total of the three products, but not for each product.

My query looks like this:

SELECT

Shop_name,

case

when prod in (1,2,3) then ‘milk_bread_butter’

when prod = 1 then ‘milk’

when prod = 2 then ‘bread’

when prod=3 then ‘butter’

else ‘none’

end as product,

sum(sales_amount) as sales

FROM

Dbo.sales

GROUP BY

Shop_name,

case

when prod in (1,2,3) then ‘milk_bread_butter’

when prod = 1 then ‘milk’

when prod = 2 then ‘bread’

when prod=3 then ‘butter’

else ‘none’

end



The problem is that I only get the lin 'milk_bread_butter' per shop. If I switch the order around in the case statement;

case

when prod = 1 then ‘milk’

when prod = 2 then ‘bread’

when prod=3 then ‘butter’

when prod in (1,2,3) then ‘milk_bread_butter’

else ‘none’



I get the sales amounts per shop for each of the products, but not for the milk_bread_butter total.



I would be very greatful for any guidance as how to solve this.

Thank you.

Best, Mal

View 3 Replies View Related

Adding A Group By Clause And Getting A Count Of A Group

Feb 6, 2008

HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned.
DECLARE @StartDate varchar(12)DECLARE @EndDate   varchar(12)DECLARE @Region    varchar(20)
SET @StartDate = '01/01/2002'SET @EndDate   = '12/31/2008'SET @Region    = 'Central'
SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName
FROM  dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid
WHERE (A.createdon >=@StartDate  AND A.createdon <= @EndDate)AND   (B.new_RegionName = @Region)AND   (A.casetypecode = 2) 
 

View 1 Replies View Related

GROUP By Clause Or DISTINCT Clause

Jul 23, 2005

Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1

View 3 Replies View Related

Having Clause Without GROUP BY Clause?

Nov 20, 2004

Hi,

What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?

eg :

SELECT SUM(col1) from test HAVING col2 < 5

SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5

I want the equivalent query in MSSQLServer for the above Oracle query.

Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.

Thanks,
Gopi.

View 3 Replies View Related

Top Clause With GROUP BY Clause

Apr 3, 2008

How Can I use Top Clause with GROUP BY clause?

Here is my simple problem.

I have two tables

Categories
Products

I want to know Top 5 Products in CategoryID 1,2,3,4,5

Resultset should contain 25 Rows ( 5 top products from each category )

I hope someone will help me soon.
Its urngent


thanks in advance

regards
Waqas

View 10 Replies View Related

Need Help With Group Clause.

Mar 23, 2008

 HiI have this query I made but I am not sure how to fully use the group by clause.  IF @option = 'day'
BEGIN
SELECT userID, SUM(Correct) AS CORRECT, SUM(Wrong) as Wrong, SUM(AssitanceNeeded) AS AssitanceNeeded,
CONVERT(VARCHAR(10),TimeDateStamp,101) As TimeDateStamp
FROM Charts
WHERE TimeDateStamp
BETWEEN DATEADD(d,-7,DATEDIFF(d,0,getdate() + 1))
AND
DATEADD(ss,-1,DATEADD(d,DATEDIFF(d,0,getdate())+ 1 , 0)) AND UserID = @UserID
GROUP BY UserID,TimeDateStamp
ENDSo I get a result like this:BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0012003/23/2008BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0012003/23/2008BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0025103/23/2008BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0020003/23/2008BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0014003/23/2008BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0012003/23/2008BB7EFE81-B532-46DC-B8D1-4E7A4186EEB0012003/23/2008 but I really just want it to be thisBB7EFE81-B532-46DC-B8D1-4E7A4186EEB001071 03/23/2008See I want everything with the same user name and date to be grouped together. But when I do that in my group clause it separates them all.  Yet if I don't have my timeDateStamp in the group clause I get a syntax error.Thanks   

View 4 Replies View Related

Cannot Use GROUP BY Clause The Way I Want To?

Jun 28, 2004

I'm trying to use a GROUP BY clause in my SQL statement when retieving information from an SQL Server DB. The only problem is that it won't let me 'SELECT' columns from the database that are not part of the GROUP BY clause. Here is my example:

This works:

SELECT ColumnA, ColumnB FROM MyTable GROUP BY ColumnA, ColumnB

This does NOT work:

SELECT ColumnA, ColumnB FROM MyTable GROUP BY ColumnA

It simply will not let me have ColumnB in the SELECT clause unless I put it in the GROUP BY clause. Is there any way around this? Because I need both columns to display in the page, but I only want to group them by one column.

I'm coming from MySQL, and in MySQL what I want to do is perfectly legal. However, in SQL Server it's not...

Any ideas?

View 3 Replies View Related

Group By Clause

Nov 2, 2007

in select apart of my statment I have the below code, but I want to exclude it from the group by clause, is there a way I can do this??


(cast(timesheethours.hoursworked as char) + '' + cast

(timesheethours.payrate as char) + ''+ objects.FileAs) as 'workers details',


Thanks

Dave,

View 4 Replies View Related

Group By Clause

May 10, 2008

Hi all,

Got a problem here, I am not quite familiar yet with the sql group by clause. As an illustration, I have a table with column StoreName and Sales.

StoreName Sales
DFA_Main 50
DFA_Main 50
DFA_Branch 60
DFA_Branch 60
DFA_OtherBranch 10
MMDA_Main 50
MMDA_Main 50
MMDA_Branch 30
MMDA_Branch 30

In my understanding if we are going to group this table by StoreName the result will be:

StoreName Sales
DFA_Main 100
DFA_Branch 120
DFA_OtherBranch 10
MMDA_Main 100
MMDA_Branch 60

But what would be the sql statement to produce an output like this:

StoreName Sales
DFA 230
MMDA 160

Is it possible for me to do that? Thanks for the help in advance.

===============
JSC0624
===============

View 1 Replies View Related

HAVING Clause Cannot Be Used Without GROUP

Oct 17, 2013

In SQL, HAVING clause cannot be used without GROUP BY operation.

View 9 Replies View Related

How To Use The Group Clause

Dec 20, 2005

Hi all,

I have this stored procedure and I want it to produce the results to GROUP BY Style1, Style2, StyleColor, Whrse_No, Bin_No ...... This procedure is used by a crystal report file to produce a report.... Below is the code:
( I think that the problem is in that some fields which are not summed have to be somehow gropued or modified
)

------------------ Seelct part simply slects several variables where sum of them are summed

select Style1 ,style2,StyleColor,Description,Whrse_No, Whrse_Desc, Bin_no, size_cd, Nbr_Sizes

, Size_Desc01, Size_Desc02, Size_Desc03, Size_Desc04, Size_Desc05
, Size_Desc06, Size_Desc07,Size_Desc08, Size_Desc09, Size_Desc10
, Size_Desc11, Size_Desc12, Size_Desc13, Size_Desc14, Size_Desc15 , OnHandQty_Total = sum ( OnHandQty_Total)
, OnHandQty_Sz1 = sum (OnHandQty_Sz1), OnHandQty_Sz2 = sum (OnHandQty_Sz2), OnHandQty_Sz3 = sum (OnHandQty_Sz3), OnHandQty_Sz4 = sum (OnHandQty_Sz4), OnHandQty_Sz5 = sum (OnHandQty_Sz5)
, OnHandQty_Sz6 = sum (OnHandQty_Sz6), OnHandQty_Sz7 = sum (OnHandQty_Sz7), OnHandQty_Sz8 = sum (OnHandQty_Sz8), OnHandQty_Sz9 = sum (OnHandQty_Sz9), OnHandQty_Sz10 = sum (OnHandQty_Sz10)
, OnHandQty_Sz11 = sum (OnHandQty_Sz11), OnHandQty_Sz12 = sum (OnHandQty_Sz12), OnHandQty_Sz13 = sum (OnHandQty_Sz13), OnHandQty_Sz14 = sum (OnHandQty_Sz14), OnHandQty_Sz15 = sum (OnHandQty_Sz15)
, Total_OnHandNeg
, CompanyName
, Date


from #rInventoryOnHandStyle_Whse

Where 1 = 1

' + @sAND10 + '

------------- Group Rule

Group By Style1, style2, StyleColor, Whrse_No, Bin_no

' + @OrderBy + '


'
--)

EXEC(@cmd)
GO

View 2 Replies View Related

SQL--- GROUP BY Clause

Mar 21, 2006

Hi,I was wondering if anyone out there can help me with this SQL problem:I have a database that has two tables: EMPLOYEE and JOB_TITLEThe EMPLOYEE Table consists of a salary and job_title _code columns,among many others; the JOB_TITLE table contains job_title_code column,among many others.The SQL problem is: Select the employees' last names and Group them bySalary within their job_title_code. I am new to SQL statements andkinda puzzled on how to solve this problem. I would appreciate any helpI can get on this. Thanks a lot in advance.

View 1 Replies View Related

Need Help Using GROUP BY Clause

Sep 24, 2006

I have two tables
A. TEST_SUBJECTS_TBL with the following columns . This table contains the subjects in a test
1. TEST_SUBJECT_ID PK
2. SUBJECT_ID FK
3. TEST_ID FK
4. PM // This is the passing marks for the subject

B. TEST_MARKS_TBL with the following columns This table stores the marks scored by students for each subject
1. TEST_SUBJECT_ID FK
2. STUDENT_ID FK
3. MARKS_OBTAINED

I need a query which gives me the max, min,avg marks obtained in each subject for a test and the total number of students who have passed in the subject

The query output should be something like this
___________________________________________________________________
SubjectID MaxMarks MinMarks AvgMarks TotalStudentsPassed
___________________________________________________________________
1 90 30 44 11 6
2 80 24 22 33 8
......
......

I can use a groupby to find max,min and avg marks but finding total students passed is posing a problem.

View 7 Replies View Related

GROUP BY CLAUSE

Sep 24, 2007

Greetings!



Why does the select statement must have the same non aggregated columns which are also part of the group by clause?



I have a feeling it's to do with the way the SQL Server database engine actually executes the query? i.e. the select part is the last thing the engine performs (after doing the joins and the filtering etc...).



Your help would be appreciated.

View 2 Replies View Related

Use Of Group By Clause

Oct 10, 2007


create table A
(
USERID int,
USER varchar(20),
TTYPE varchar(20),
DETAIL varchar(20),
);


insert into A(
1,'X','Credit','Amount xxx',
1,'X','Debit', 'Amount xxx',
2,'Y','Debit', 'Amount xx',
2,'Y','Debit', 'Amount xxx',
1,'X','Debit', 'Amount xxxx',
1,'X','Credit', 'Amount xxxx',

);

Create table B
(
USERID int,
TRANSACTION int
);


insert into B select USERID, COUNT(case when TTYPE='Credit' 1 else null end) as TRANSACTION from A group by USERID


[Error Code: 8118, SQL State: S1000] Column 'A.DETAIL' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Any suggestions......

View 2 Replies View Related

Need Help Using GROUP BY Clause

Sep 24, 2006

I have two tables
A. TEST_SUBJECTS_TBL with the following columns . This table contains the subjects in a test
1. TEST_SUBJECT_ID PK
2. SUBJECT_ID FK
3. TEST_ID FK
4. PM This is the passing marks for the subject

B. TEST_MARKS_TBL with the following columns This table stores the marks scored by students for each subject
1. TEST_SUBJECT_ID FK
2. STUDENT_ID FK
3. MARKS_OBTAINED

I need a query which gives me the max, min,avg marks obtained in each subject for a test and the total number of students who have passed in the subject

The query output should be something like this
___________________________________________________________________
SubjectID MaxMarks MinMarks AvgMarks TotalStudentsPassed
___________________________________________________________________
1 90 30 44 11 6
2 80 24 22 33 8
......
......

I can use a groupby to find max,min and avg marks but finding total students passed is posing a problem. How do i find the total students passed ?

View 2 Replies View Related

Duplicates And The GROUP BY Clause

Nov 12, 2003

I am making a website where users go to a page that lists every Program in their area. The first time the page loads they see all the Programs, then then can filter it down with drop down lists. I have everything working except for the Category because some programs have more than one category. The select is working good but I get duplicates.

Here it is:

SELECT DISTINCT
p.ProgramID,
p.ProgramName,
p.ProgramCity,
p.ProgramState,
p.ProgramCountyID,
p.ProgramHours,
p.ProgramContactName,
p.ProgramPhone,
p.ProgramEmail,
p.ProgramGrades,
p.ProgramTransportation,
pc.ProgramID,
pc.CategoryID

FROM
Programs p,
ProgramCategories pc

WHERE
p.ProgramCountyID IS NOT NULL AND
p.ProgramCity IS NOT NULL AND
p.ProgramHours IS NOT NULL AND
p.ProgramGrades IS NOT NULL AND
p.ProgramTransportation IS NOT NULL AND
p.ProgramID = pc.ProgramID AND
pc.CategoryID IS NOT NULL


GROUP BY
p.ProgramID

ORDER BY
p.ProgramName ASC



When I have just p.ProgramID in the GROUP BY clause, I get the error:

"column name" is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

But when I put all the column names in the GROUP BY clause, I still get duplicates. What can I do to stop this. When the user selects a category the pc.CategoryID IS NOT NULL changes to pc.CategoryID = 3 (or whatever they select) and everything works the way its supposed to. I just want each individual program to show only once when the page first loads.

Thanks for your time
Dave

View 2 Replies View Related

GROUP BY/ HAVING CLAUSE Problem

Sep 27, 2005

I'm trying to set up my adhoc query to return just one single record, which is aliased as 'foreign' in my sql statement (which is just the total amount of foreign overseas orders for just one day. All Sale_Type_Ids over 2 [integer datatype] are foreign orders):
SELECT     SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) AS foreignFROM         Orders INNER JOIN                      Processing ON Orders.ID = Processing.Order_IDWHERE     (Processing.Orderdate = '20050915') AND (Processing.status = 1)GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL ENDHAVING      (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) >= 0)
..but my resultset is returning two records. If I remove the HAVING clause, it will return three records, with one being blank.???.netsports

View 5 Replies View Related

Group By Clause Limitation

Oct 14, 2006

Code is:
select
case when ItemCode is null then '-'
else ItemCode
End,
case when sum(RecdQty) is null then '-'
else sum(RecdQty)
End
from ItemMaster where ItemCode='V001' group by ItemCode

Problem Statement:
If query is not getting any records for above mentioned condition, then I want zero to be displayed if datatype is int (i.e. for sum(RecdQty) field) and '-' to be diplayed if datatype is varchar (i.e. for ItemCode field).
In this situation, "ItemCode is null" and "sum(RecdQty) is null" conditions are not been utilised.
Is this a limitation of case or group by clause?

View 2 Replies View Related

Using Group By And Order By Clause

May 21, 2014

I have a a grid (Fig-1) where i have LineID and corresponding RankValue. I want to sort out the Grid like (Fig-2) where It will be sorted based on Rank Value(Higher to lower) but LineID group should maintain. I am using SqlServer 2008.

View 3 Replies View Related

Best Practices: GROUP BY Clause

May 20, 2004

I was wondering what the best way to write a GROUP BY clause when there are many (and time consuming) operations in the fields by grouped.

Fictious example:

SELECT DeptNo, AVG(Salary) FROM Department GROUP BY DeptNo;

This will give me the average salary per department. Let's say, however that
I had 10-15 fields being returned (along with the AVG(Salary)) and some fields even had operations being performed on them. Is it better to create a temporary table to calculate the sum per department (or a VIEW) and then
perform a JOIN with the rest of the data?

Fictious example:

SELECT DATENAME(y, StartDate), DATENAME(m, StartDate), DATEPART(d, StartDate), SUBSTR(DeptName, 1, 10), SomeFunction(SomeField), SomeFunction(SomeField), AVG(Salary)
GROUP BY DATENAME(y, StartDate), DATENAME(m, StartDate), DATEPART(d, StartDate), SUBSTR(DeptName, 1, 10), SomeFunction(SomeField), SomeFunction(SomeField);

Am I better off writing my query this way or using a JOIN on some temporary table or view?

Thanks

View 14 Replies View Related

Error In Group By Clause...

Apr 12, 2007

When trying to build a report in sql server 2005 business intelligence dev studio (to deploy to report server) based on the following query I get an error msg.

Error in group by clause.
Unable to parse query text.

SELECT sub.*, ap.apat_code
FROM k_apptreg ap,
(SELECT R.REFL_DATE, R.EVENT_NO, C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, P.SURNAME AS CLINICIAN,
R.DEPT_CODE, D .DEPT_TITLE, R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE, MAX(A.APPT_DATE)
AS LAST_APPT
FROM ORACARE.K_REFLREG R, ORACARE.K_CPIREG C, ORACARE.K_DEPTLIST D, ORACARE.K_PROFREG P, ORACARE.K_APPTREG A
WHERE R.PT_CODE = C.PT_CODE AND R.DEPT_CODE = D .DEPT_CODE AND R.HOSP_CODE = D .HOSP_CODE AND
R.CONS_MD_CODE = P.MPROF_CODE AND R.EVENT_NO = A.EVENT_NO (+) AND (R.ANNLAPPT_DATE < :PARAM1) AND
a.xtend_ind = 'Y'
GROUP BY R.REFL_DATE, R.EVENT_NO, C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, P.SURNAME, R.DEPT_CODE,
D .DEPT_TITLE, R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE
ORDER BY r.event_no) sub
WHERE ap.event_no (+) = sub.event_no AND sub.last_appt = ap.appt_date (+) AND xtend_ind = 'Y'
ORDER BY sub.annlappt_date

View 2 Replies View Related

Group By Clause Query Help

Jul 23, 2005

This is my queryselect ano,max(date),a_subject from MY_TAB where table_name='xyz' andano=877group by a_subject,ano order by a_subjectANOmax(Date)A_Subject8772005-01-20 00:00:00.000Subject_18771900-01-01 00:00:00.000Subject_28772004-12-20 00:00:00.000Subject_38772005-01-19 00:00:00.000Subject_4--------------------------------------------------------------------------When I put the status column in, it fetches all the rows.select ano,max(date),a_subject,status from MY_TAB wheretable_name='xyz' and ano=877 group by a_subject,ano,status order bya_subjectANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8771900-01-01 00:00:00.000Subject_3Not Started8771900-01-01 00:00:00.000Subject_4Not Started8772005-01-19 00:00:00.000Subject_4Not Started-----------------------------------------------------------------------now what i want isANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8772005-01-19 00:00:00.000Subject_4Not StartedThanks a lot for your help.AJ

View 2 Replies View Related

Using UPDATE On A GROUP BY Clause

Sep 29, 2007

I'd like to update the attribute Active of the table COMPANY_BRANCH to the value 0 (or false, since it is a bin field) on the below recordset:

SELECT COMPANY_BRANCH.CompBranchID, MAX(PLACEMENT.ExpirationDate) AS Expr1

FROM COMPANY_BRANCH INNER JOIN

PLACEMENT ON COMPANY_BRANCH.CompBranchID = PLACEMENT.ProducerBranchID

GROUP BY COMPANY_BRANCH.CompBranchID

HAVING (MAX(PLACEMENT.ExpirationDate) < DATEADD(yyyy, - 1, GETDATE()))

yet, I am unable to understand the concept on how this can be done. If I put it into query designer and change the query type to update, the code that gets generated is not correct - can someone help me?

View 8 Replies View Related

Group By Clause Killing Performance

Jun 15, 2007

I have recently started working with a new group of people and I find myself doing a lot of reporting. While doing this reporting I have been writing a TON of sql. Some of my queries were not performing up to par and another developer in the shop recommended that I stay away from the "GROUP BY" clause.
Backing away from the "GROUP BY" clause and using "INNER SELECTS" instead as been more effective and some queries have gone from over 1 minute to less that 1 second.
Obviously if it works then it works and there is no arguing that point. My question to the forum is more about gather some opinions so that I can build an opinion of my own.
If I cannot do a reasonable query of a couple of million records using a group by clause what is the problem and what is the best fix?
Is the best fix to remove the "GROUP BY" and write a query that is a little more complex or should I be looking at tuning the database with more indexes and statistics?
I want to make sure that this one point is crystal clear. I am not against following the advice of my coworker and avoiding the "GROUP BY" clause. I am only intersted in listening to a few others talk about why the agree or disagree with my coworked so that I can gain a broader understanding.

View 6 Replies View Related

SQL Query Problem In Group By Clause

Jan 26, 2004

I am developing Staff Allocation System,
database is sql server 2000.
I have problem in retrieve the staff informations,

employee working which Project and what project have assign to him, what is his assign project or contract no,

One employee working more then one project, retrieve information one employee how many projects are working,

What is his approved position, what is his assign position.
It the main data have to retrieve, as well as retrieve all fields which related to those tables.

I use this query.

select name,apppos approved_position,appcont approved_contract,appdate employee_appr_date,munref Municipality_Ref,dcilref DCIL_REF,projtype Project_Type,strdate Project_str_date,comdate Projcet_comp_date,extdate Proejct_ext_date,dept,emptype Employee_Type from contract,emp,apprecords where contract.rec_id=emp.rec_id and emp.rec_id=apprecords.rec_id and apprecords.name='dewachi'


above query retrieve no data,

how can use group by clause in the above query ?

group by apprecords.appcontract

group by clause give error.

above query have to retrieve data from the three tables, I have four tables, what query I use so that all four tables data retrieve like this.


Name, approved_position, approved_contract,assign_position,assign_contract,startdate,completion_date,........ and so on…
Group by apprecords.appposition
……….


Contract Table (basic data entry contract table)
-------------------------------------------------------
rec_id
Contract No.
ProjectType
StartDate
CompletionDate
ExtendedDate


Employee Table (basic data entry employee table)
---------------------------------------------------------
rec_id
EmpNo
Name
Position
Department
EmployeeType


Approved Records Table (in this table all information about
the employee and his approved
position and contract )
------------------------------------------------------------------------
rec_id
Name
Approved Date
MunicipalityRefNo
DCILRefNo
ApprovedPosition
ApprovedContract


Assign Project Table (in this table all information about the
employee his assign the project)
--------------------------------------------------------------------
rec_id
Name
AssignPosition
AssignContract
EmpProjectStartDate
EmpProjectEndDate
ShiftNo
ProjectStatus



Regards.


MATEEN

View 6 Replies View Related

GROUP By Clause, /sub Query Problems

Aug 15, 2005

I'm trying to list salesreps (if they have any sales for a particular date) with their total sales amounts for a queried date, but when running this sql string in QueryAnalyzer, it says there is an error with syntax on Line 1 near "s" :SELECT o .Rep_ID, o .ID, s.ID, SUM(b.orderamount) AS totalsales, b.order_ID
FROM (SELECT b.Deal_ID
FROM btransactions b
WHERE b.BoardDate = '20050815') SalesReps s INNER JOIN
orders o ON o .Rep_ID = s.ID INNER JOIN
b ON o.ID = b.Deal_ID
GROUP BY d .Rep_ID, d .ID, s.ID, b.order_ID
HAVING (SUM(b.orderamount) > 0)???.NetSports

View 1 Replies View Related

Group By Clause Based On Condition

Jun 14, 2014

tblScore contains score for each problem

id problemID score
------------------------
1 1 10
2 2 30

tblSubmission contains problem submissions for each user

id user problemID accepted
-----------------------------------------------
1 UserA 1 0
2 UserA 1 0
3 UserA 1 1
4 UserA 2 1
5 UserB 1 0
6 UserB 1 1
7 UserB 2 1

For UserA :
- For problemID 1
-> submitted three times
-> rejected for first two submission and accepted on third submission.
- For problemID 2
-> submitted one time
-> accepted on first submission

For UserB :
- For problemID 1
-> submitted two times
-> rejected for first submission and accepted on second submission.
- For problemID 2
-> submitted one time
-> accepted on first submission

Now I would like to process the table and want to get the following result :

user Score
--------------------------
UserA 36 (6 + 30)
UserB 38 (8 + 30)

Explanation :
- For each rejected submission, a -2 point penalty.
- UserA have submitted probelmID 1
- > score of problemID 1 is 10.
- > first two times rejectd
- > third time accepted.
-> score = 10 - 4 = 6
- UserA have submitted problemID 2
- > score of problemID 2 is 30
- > first time accepted. No penalty will be counted
- > score = 30

so final score for UserA = 30 + 6 = 36

Similar for UserB.

View 2 Replies View Related







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