GROUP By Clause Or DISTINCT Clause
Hi, can anyone shed some light on this issue?
SELECT Status from lupStatus
with a normal query it returns the correct recordcount
SELECT Status from lupStatus GROUP BY Status
but with a GROUP By clause or DISTINCT clause it return the recordcount
= -1
View Complete Forum Thread with Replies
Related Forum Messages:
Having Clause Without GROUP BY Clause?
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 Replies !
Top Clause With GROUP BY Clause
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 Replies !
Not Part Of DISTINCT Clause But I Do Want It Selected
I get the rows(records) I want by removing the RequestDateTime column from the Select query SELECT distinct et.configname as 'Part Number', substring(email,(charindex('@',email)+1),len(email)) as 'email ending',u.company_name as 'Company',u.city as 'City',u.country as 'Country' --,RequestDateTime as 'Date Downloaded'from new_products.dbo.modelrequests etleft outer join cc_host.dbo.usr_list u on et.username = convert(varchar(50),u.[user_id])where interfacename like '%download%'and result=0and u.country = 'JP'and CONVERT(DATETIME, RequestDateTime, 102)>= convert(DATETIME,'12/5/2007', 102)GROUP BY email,et.configname,u.company_name,u.city,u.country,requestdatetime I want to show the RequestDateTime for the 1600 records that do get selected. How can I put RequestDateTime back in the resulting table output without it being part of the Distinct clause?
View Replies !
When Does Ssrs Use A DISTINCT Clause In It's Queries?
I am having a heck of a time figuring out what controls how/when the generated SQL for a report puts a DISTINCT clause in front of it. For instance, not that this report makes any sense, but I have 58 rows in my fact table/entity €“ If I pull in a lookup field and execute, the distinct is put in the query and I basically get a list of the possible domain values. It runs the whole joined table query to get them, but it does list (in this case) just 4 records. Now I put in the primary ID of the fact entity and the distinct goes away and I get my 58 rows. If I put two lookup fields, the DISTINCT is back. If I pull in the description field (text string just a direct source field mapping not part of the identifying attributes), the distinct is there. If I pull in the Company Name field on a different entity (which is essentially the same as pulling in Description only it is part of the identifying attributes), there is no DISTINCT. I can pull in all my fields on this entity and none of them drive the distinct. And I swear (ok, I am probably lying but not on purpose) the field/attribute and roles properties are all the same on the attributes. But you get my general question/situation... Any insight for me? Does it have to do with how I am building the report rather than the underlying model? Thanks in advance,
View Replies !
Adding A Group By Clause And Getting A Count Of A Group
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 Replies !
How To Use ORDER BY Clause In An SELECT DISTINCT Sql Query When AS SINGLECOLUMN Is Defined?
Hi, I wonder if its possible to perform a ORDER BY clause in an SELECT DISTINCT sql query whereby the AS SINGLECOLUMN is used. At present I am recieving error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. My guess is that I cant perform the Order By clauses because it cant find the columns individually. It is essentail I get this to work somehow... Can anyone help? Thanks in advance Gemma
View Replies !
Need Help With Group Clause.
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 Replies !
Cannot Use GROUP BY Clause The Way I Want To?
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 Replies !
SQL--- GROUP BY Clause
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 Replies !
Group By Clause
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 Replies !
Need Help Using 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.
View Replies !
Need Help Using 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 ?
View Replies !
Need Help Using 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 7 2 80 24 22 33 11 ...... ...... I can use a groupby to find max,min and avg marks but finding total students passed is posing a problem.
View Replies !
Use Of Group By Clause
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 Replies !
GROUP BY CLAUSE
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 Replies !
How To Use The Group Clause
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 Replies !
Group By Clause
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 Replies !
GROUP BY/ HAVING CLAUSE Problem
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 Replies !
Duplicates And The GROUP BY Clause
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 Replies !
Group By Clause Query Help
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 Replies !
Group By Clause Limitation
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 Replies !
Best Practices: GROUP BY Clause
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 Replies !
Using UPDATE On A GROUP BY Clause
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 Replies !
Error In Group By Clause...
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 Replies !
Group By Clause Killing Performance
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 Replies !
GROUP By Clause, /sub Query Problems
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 Replies !
SQL Query Problem In Group By Clause
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 Replies !
Many Fields Update From A Group By Clause
Hi All,In Oracle, I can easily make this query :UPDATE t1 SET (f1,f2)=(SELECT AVG(f3),SUM(f4)FROM t2WHERE t2.f5=t1.f6)WHERE f5='Something'I cannot seem to be able to do the same thing with MS-SQL. There areonly 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 t2WHERE t2.f5=t1.f6)WHERE f5='Something'and then the same statement but with f2, and2)UPDATE t1 SET f1=(SELECT AVG(f3)FROM t2WHERE t2.f5=t1.f6),f2=(SELECT SUM(f4)FROM t2WHERE t2.f5=t1.f6)WHERE f5='Something'Is there a way with MS-SQL to do the Oracle equivalent in this case ?Thanks,Michel
View Replies !
Median Query With Group By Clause
Hi,I have a view in which I have 3 cols...(pno,ptno,diff)..diff is thedifference in time in minutes.I want to calculate Median(diff) groupby pno,ptno...using a sql query for SQL server...Any help is greatly appreciated..ThanksAJ
View Replies !
Alternative Solution To GROUP BY ALL Clause
hi, I was using GROUP BY ALL clause in my query .But sql server CE doesn't support ALL keyword. I want to know is there some other solution to using ALL keyword in SQL CE. I want to retrieve those rows which don't satisfy the where prod_position=1 condition along with those who satisfy it. my query is select prod_desc ,count(tbl_asa_tns_call_Detail.prod_id) as pri from tbl_Asa_prod_brnd, tbl_asa_tns_call_Detail where prod_position=1 and tbl_Asa_prod_brnd.prod_id=tbl_asa_tns_call_detail. prod_id and call_id in(select call_id from tbl_asa_tns_call where (day(call_date) between day('2001-01-01 00:00:00') and day ('2001-03-20 00:00:00')) and (month(call_date) between month('2001-01-02 00:00:00') and month('2001-03-20 00:00:00')) and (year(call_date) between year('2001-01-02 00:00:00') and year('2001-02-08 00:00:00'))) group by all prod_desc order by prod_desc
View Replies !
Help With Simple Query Using Group By And Where Clause
have a table with sale_id, date, sales_person_id i need to find out the sales_person_id's who did 1 sales every month from jan 2003 and another query who did a sales every quarter. How many sales person have atleast one sale every month (excluding prints) for either 2003, 2004, or 2005? How many sales person had atleast 25 sales each year 2003-2005
View Replies !
Default_Schema Clause Cannot Be Used With A Windows Group
Hi All, I am getting error when I try to create a windows group and set default schema to db_owner. Error message Alter failed for user 'Domainuser'.(Microsoft.Sqlserver.SMO) Additional information An exception occured while executing a Transact-SQL statement or batch. (Microsoft.Sqlserer.ConnectionInfo) The DEFAULT_SCHEMA clause cannot be used wth a windows group or with principals mapped to Certificates or asymmetric keys.(Microsoft SQL Server, Error:15259) Please help. Regards Hassan
View Replies !
Using Group By Clause In Union Query
Hi all, I have two table having datas like Table1 -------------------------------------------------------------------- A C1 C2 C3 C4 -------------------------------------------------------------------- x 0 0 3 2 x 0 1 0 2 x 0 0 2 1 y 1 5 2 0 Table2 -------------------------------------------------------------------- A C1 C2 C3 C4 -------------------------------------------------------------------- x 0 0 1 4 y 1 0 3 1 y 1 2 0 0 y 0 0 5 1 select * from( select A,C1,C2,C3,C4 from Table1 group by A union select A,C1,C2,C3,C4 from Table2 group by A )as t Result: -------------------------------------------------------------------- A C1 C2 C3 C4 -------------------------------------------------------------------- x 0 1 5 5 y 1 5 2 0 x 0 0 1 4 y 2 2 8 2 But i need the result like i.e grouped by column 'A' -------------------------------------------------------------------- A C1 C2 C3 C4 -------------------------------------------------------------------- x 0 1 6 9 y 3 7 10 2 select * from( select A,C1,C2,C3,C4 from Table1 group by A union select A,C1,C2,C3,C4 from Table2 group by A )as t group by A The above query gives the following error [Error Code: 8120, SQL State: S1000] Column 't.C1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Please help me out. -Anand
View Replies !
Writing View With 'Group By' Clause
Hi All, I've the following view in my DB and I need to modify it in such a way that it should be grouped by 'categoryId' and display the count of rows that come under each categoryId. In brief, I need to use 'Group By' clause and 'Count/SUM' aggregate function. Here is my view. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MessageCategoryView]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[MessageCategoryView] GO CREATE VIEW MessageCategoryView AS SELECT m.subjectSUBJECT, m.senderidSENDERID, m.createdateCREATEDATE, m.completedateCOMPLETEDATE, m.format MSGFORMAT, m.isconfidentialISCONFIDENTIAL, m.queueidQUEUEID, mc.messageid MESSAGEID, mc.categoryid CATEGORYID, cv.label CATEGORYLABEL, cv.hierarchystring HIERARCHYSTRING, cv.hotkeys HOTKEYS FROM kc_message m JOIN kc_messagecategory mc on m.messageid = mc.messageid JOIN CategoryView cv on mc.categoryid = cv.categoryid WHERE mc.type not in (3,5) Can anyone please help me out in resolving this. Thanks in advance, Sujatha
View Replies !
Query Based On Group By Clause
Hello! suppose i have two tables, table1 columns(empcode (pk), empDept) and table2 columns(empcode (FK),Date,Attendance) i wanted to write a query to get output like DEPT ABSENT ----------------------------- Accounts 10 EDP Section 0 ** Admin 2 Stationary 0** if no employee is absent in the department it has to display Zero
View Replies !
Age Old Question About GROUP BY Clause (i Think) - Probably Easy Answer
How does one get the primary key of the row that is joined in via agroup by aggregate clause when the aggregate is not performed on theprimary 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 aview that gets the first time each person Visited:selectp.PersonID,min(v.VisitDate)fromVisit vjoinPerson p on p.PersonID = v.PersonIDgroup byp.PersonIDThe problem is that I would like to return the VisitID in theresultset, but when I do it expands the query since I have to also putit 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 Replies !
Does The Group By Have To Include All Fields From The SELECT Clause?
hey all, say i have the following function SELECT 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, F1ADR_ADDRESS.CONTACT_TITLE, GLF_CHART_ACCT.ACCNBRI, F1ADR_ADDRESS.ENTITY_UNIQUE_NBR FROM GLF_CHART_ACCT 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?? Cheers, Justin
View Replies !
Sub Queries, Aggregate Functions && Group By Clause
Hi Guys, I am having trouble with a particular query that is beyond my scope of understanding. Basically I need to pull sales records based on the following criteria: I have CustomerID, InvoiceNumber, ContractEndDate, MobileNumber, etc.. Customers recontract their mobile phone plans through us, and we have a new sales record for each time they recontract. For example, CustomerNumber 123 has recontracted 3 times.. once on 2006-01-01, then on 2007-02-12, and finally on 2008-02-15.. So they have a 12 month contract each time.. then come in to recontract it. So.. a customer has a single Customer Detail record, but may have many sales records attached. And a customer may have several sales for the SAME mobile phone number. Currently to pull ALL sales records for all customers, my query is this: Code: SELECT xxx.CustomerID AS xxx_CustomerID, xxx.Invoice AS xxx_Invoice, yyy.PhoneType AS yyy_PhoneType, yyy.PlanType AS yyy_PlanType, yyy.ContractEnds AS yyy_ContractEnds, yyy.MOB AS yyy_MobileNumber FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice WHERE yyy.ContractEnds IS NOT NULL AND xxx.CustomerID IS NOT NULL We want to get a list of customers that we can call to recontract, based on the ContractEnd field. However, we want UNIQUE mobile phone numbers, with the LATEST ContrtactEnd date. So, Customer 123 has 6 sales, for 2 unique Mobile numbers, the sql may be like: Code: SELECT MAX(yyy.ContractEnds) AS LatestCED, yyy.MOB FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice WHERE xxx.CustomerID='123' GROUP BY yyy.MOB Now, this works fine, and of course if i remove the WHERE clause, it collects all unique mobiles, with latest ContractEnd date for each, for all customers. (Customer 123 displays 2 mobile numbers, each with the LATEST ContractEnd date) BUT i need this information ALONG WITH the other fields (xxx.CustomerID, xxx.Invoice, yyy.PhoneType, yyy.PlanType) and i have tried a few ways of doing it, but can't get my head around it.. Keep getting errors about Aggregate functions and Group By clause, and i understand why i am getting them, just cant think of any alternative query. Can anyone please help me! Thanks guys, Mick
View Replies !
Using A Field Alias For A CASE Statement In A GROUP BY Clause
When I created a CASE statement (This is at work, Pat:)) it is about 30-40 lines long. I gave it a name and set the name = to the case statement: ie, SELECT fieldname1 = CASE WHEN condition THEN 'blah blah' WHEN condition THEN 'blah blah' WHEN condition THEN 'blah blah' ELSE thisandthat END , fieldname2 , fieldname3 FROM tablename1 GROUP BY CASE WHEN condition THEN 'blah blah' WHEN condition THEN 'blah blah' WHEN condition THEN 'blah blah' ELSE thisandthat END, , fieldname2, fieldname3 etc. The long CASE statement in my GROUP BY is awkward to me. Is this the only way to do it? I tried using the fieldname1 but it comes back as an invalid field name and asks for the "expression". Regards, Dave
View Replies !
SQL Statement: Group By And Where Clause Do Not Display Null Rows
The following is a simplified version of my SQL statement. I am attempting to do a simple count(*) with two groupings and a where clause. This is Select command for a GridView. However, I am unable to display zeros. The rows are completely missing and I would greatly appreciate someone's help. I have already tried Group By All, but that, unfortunately, does not work. Here is my SQL statement: "SELECT [GENDER], [RACE], COUNT(*) FROM [TABLE] WHERE ([COLUMNNAME] ='SOMETHING') GROUP BY [GENDER], [RACE]" Thanks for the help in advance!
View Replies !
Filtering Results In The Where Clause Vs A Having Clause
I am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following. When on SQL Server 2000 the following statement ran without issue: UPDATE dbo.Track_ID SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed WHERE Processed = 0 AND LegNum = 1 AND TrackID IN ( SELECT TrackID FROM dbo.Track_ID GROUP BY TrackID HAVING MAX(LegNum) = 1 AND TrackID + 'x1' IN ( SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID)) FROM dbo.Track_ID INNER JOIN dbo.transactions ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id GROUP BY dbo.Track_ID.TrackID ) ) Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time. I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same? UPDATE dbo.Track_ID SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed WHERE Processed = 0 AND LegNum = 1 AND TrackID IN ( SELECT TrackID FROM dbo.Track_ID WHERE TrackID + 'x1' IN ( SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID)) FROM dbo.Track_ID INNER JOIN dbo.transactions ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id GROUP BY dbo.Track_ID.TrackID ) GROUP BY TrackID HAVING MAX(LegNum) = 1 )
View Replies !
Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause
2 examples: 1) Rows ordered using textual id rather than numeric id Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v order by v.id Result set is ordered as: 1, 11, 2 I expect: 1,2,11 if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine. 2) SQL server reject query below with next message Server: Msg 169, Level 15, State 3, Line 16 A column has been specified more than once in the order by list. Columns in the order by list must be unique. Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v cross join ( select 1 id union select 2 id union select 11 id ) u order by v.id ,u.id Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine. It reproducible on Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) and Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) In both cases database collation is SQL_Latin1_General_CP1251_CS_AS If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again. Could someone clarify - is it bug or expected behaviour?
View Replies !
SQL Inner Join Clause And The Where Clause
Hi everyone, I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ? Thanks
View Replies !
Diff In On Clause And Where Clause?????
hi.. i have basic question like what is differance between conditions put in ON clause and in WHERE clause in JOINS???? see conditions that shown in brown color select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate, d1.Audit, d1.ExpirationDate, d1.Indicator from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2 on d1.SummaryID=d2.SummaryID AND d1.ListType = d2.ListType AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null) AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null) whered1.ImplicitID >= d2.ImplicitID AND (d1.SourceID<>d2.SourceID OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL) OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL) ) select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate, d1.Audit, d1.ExpirationDate, d1.Indicator from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2 on d1.SummaryID=d2.SummaryID AND d1.ImplicitID = d1.ImplicitIDAND d1.ListType = d2.ListType AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null) AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null) whered1.ImplicitID >= d2.ImplicitID AND (d1.SourceID<>d2.SourceID OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL) OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL) ) another thing... if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove d1.ImplicitID >= d2.ImplicitID from WHERE clause????
View Replies !
Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
View Replies !
Help With A WHERE Clause
I have an insert statement that reads: SELECT AppointmentID, PatientNo, PatientSurname, PatientForename, ConsultantName, HospitalName, Date, CONVERT (varchar, Time, 8), AppointmentStatus FROM [Appointment] WHERE ([AppointmentId] = @AppointmentId) I also need to add another WHERE clause. This clause will mean that if the date is within 14 days of the actual date it will not ba able to be selected need help writing this not sure how to write it Thanks in advance Mike.
View Replies !
Sql Where Clause - Help
Hey guys, I'm a bit weak when it comes to doing ands and or's. I know what i want, but when I put it into statement, i dont get the results that i want. I have 3 fields in my where clause. ID, LW, and LWU. The code is as follows:WHERE (LASTVISIT BETWEEN '1 / 1 / 95 12 : 00 : 00 AM' AND '1 / 1 / 06 12 : 00 : 00 AM') AND (ID NOT LIKE '%6%') AND (ID NOT LIKE '%7%') AND (ID NOT LIKE '%8%') AND (LW <> 1) AND (LWU <> 'test') OR (LASTVISIT BETWEEN '1 / 1 / 95 12 : 00 : 00 AM' AND '1 / 1 / 06 12 : 00 : 00 AM') AND (ID IS NULL) AND (LW <> 1) AND (LWU <> 'test') I have a range of dates that I want to grab, in there I do not want any records where ID has 6,7,8 and I only want records where LW does not equal 1. UP to this point, it works fine. I get all the records that only return these values. However, the moment I add where LWU does not equal 'test'. it does not return the values I want. Furthermore, why can I not put this whole string into one and clause? I never understood why I had to create a second line following OR. the longer this query gets the more I get confused. Any help?
View Replies !
Like Clause
I'm trying to do a simple ... SELECT ... FROM .... WHERE ... LIKE clause and i think my syntax is off. WHile using sql server ...... is the syntax Where Name LIKE '%variable%' ?????? Or should I be using something differnent. Thank you in advance for any help.
View Replies !
|
TRACKER
