UPDATE t1 SET (f1,f2)=(SELECT AVG(f3),SUM(f4)
FROM t2
WHERE t2.f5=t1.f6)
WHERE f5='Something'
I cannot seem to be able to do the same thing with MS-SQL. There are
only 2 ways I've figured out, and I fear performance cost in both cases,
which are these :
1)
UPDATE t1 SET f1=(SELECT AVG(f3)
FROM t2
WHERE t2.f5=t1.f6)
WHERE f5='Something'
and then the same statement but with f2, and
2)
UPDATE t1 SET f1=(SELECT AVG(f3)
FROM t2
WHERE t2.f5=t1.f6),
f2=(SELECT SUM(f4)
FROM t2
WHERE t2.f5=t1.f6)
WHERE f5='Something'
Is there a way with MS-SQL to do the Oracle equivalent in this case ?
I'd like to have all distinct recordIDs with relevant text associated with them. Each record has 3 text boxes in different languages. Each text in different language is defined by an AttributeDefinitionID. This is my query:
Select a.entryID, g.GroupName, c.CategoryName as ExperienceType, e.AttributeValue as EnglishWording, e1.AttributeValue as GermanWording, e2.AttributeValue as RussianWording, From Entry as a inner join entrycategory as b on b.entryid = a.entryid
[Code] ....
but in the results I get additional rows for each record even if the record doesnt have all three text boxes populated and there is only EnglishText for example.
EntryID GrouPName EnglishWording GermanWording RussianWording 1586 Red abc NULL NULL 1586 Red NULL NULL NULL 3566 Yellow NULL Hallo Welt NULL 3566 Yellow NULL NULL NULL 3566 Yellow Hello world NULL NULL 3566 Yellow Hello world Hallo Welt NULL
1586 should only return the first line with English wording. 3566 should return the last line that shows both English and German wording populated
INNER JOIN F1ADR_ADDRESS ON (GLF_CHART_ACCT.CHART_NAME = F1ADR_ADDRESS.ENTITY_KEY1) AND (GLF_CHART_ACCT.ACCNBRI = F1ADR_ADDRESS.ENTITY_KEY2)
GROUP BY GLF_CHART_ACCT.DESCR1, F1ADR_ADDRESS.ADDR1, F1ADR_ADDRESS.ADDR2, F1ADR_ADDRESS.ADDR3, F1ADR_ADDRESS.ADDR_CITY, F1ADR_ADDRESS.ADDR_STATE, F1ADR_ADDRESS.POST_CODE, F1ADR_ADDRESS.PHONE_NBR, F1ADR_ADDRESS.FAX_NBR, F1ADR_ADDRESS.EMAIL_ADDR_NAME, F1ADR_ADDRESS.CONTACT_NAME, GLF_CHART_ACCT.ACCNBRI, F1ADR_ADDRESS.CONTACT_TITLE, GLF_CHART_ACCT.CHART_NAME, F1ADR_ADDRESS.ENTITY_UNIQUE_NBR, GLF_CHART_ACCT.SELN_TYPE1_CODE
HAVING CHART_NAME='ARCHART' AND GLF_CHART_ACCT.DESCR1 <> '' AND GLF_CHART_ACCT.SELN_TYPE1_CODE = 'Trade' AND GLF_CHART_ACCT.DESCR1 LIKE '%" + Search + "%' ORDER BY GLF_CHART_ACCT.DESCR1;
I get errors if not all the fields are included in the group by clause.
what i dont get is why i have to create seperate groups for this query...or am i reading it wrong??
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?
I've just written a query that successfully brings back the data from one table based on the information from another. Basically we have been given a table of information and need to update certain fields in our user_group table with the new info.
Here is the SELECT statement SELECT user_group.id, user_group.name, user_group.description, Consultants.description AS Expr10, user_group.btype, user_group.rootmenu, user_group.intra_user, user_group.primary_g_id, user_group.fname, user_group.lname, user_group.ntlogon, user_group.lang_id, user_group.[external], user_group.title, user_group.work_tel, user_group.work_fax, user_group.work_ext, user_group.mobile, user_group.sex, user_group.add2, user_group.add3, user_group.town, user_group.county, user_group.pcode, user_group.private_flag,
[code]....
We want to update the 'description' on the user_group table with the 'description' from the 'consultants' table. To test this, we only want to write the UPDATE so that it changes the description where the name is 'Adam Froth. The UPDATE statement that we've written is
UPDATE user_group SET user_group.description = Consultants.description FROM user_group INNER JOIN Consultants ON user_group.description = consultants.description WHERE name like 'Adam Froth%'
but it keeps erroring and saying that it could 'Not be bound'.
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)
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
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
Pardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.
In a nutshell, how can I write something like: UPDATE "TABLENAME" SET DATAFIELD1 = DATAFIELD2 WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)
I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.
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
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...
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
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.
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.
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...).
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.
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 ?
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.
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.
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
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?
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.
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?
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
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
Lets say I have a list of customers, order date, and an item they ordered. I want to grab the MIN order date by customer, and the associated product. If I do:
Code: SELECT customer,min(order date),item FROM mytable GROUP BY customer
I get an error because item is not aggregated. But I don't want to group on it and i don't want to select a min/max of it. i just want it to carry over the item that is associated with that min order date.
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.
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 ……….
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
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