Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
udaymahajan
Starting Member
17 Posts |
 Posted - 2008-03-31 : 08:07:54
|
| in my sql query,when it run then it displays records as followsdt_id daily_type dates emp_mgmt_id emp_name etc--------48 Electrical 03/14/2008 22 abc ----------49 Mechanical 03/14/2008 35 xyz -------------48 Electrical 03/14/2008 22 abc ----------49 Mechanical 03/14/2008 35 xyz -------------i want to display records only once like as follows dt_id daily_type dates emp_mgmt_id emp_name etc--------48 Electrical 03/14/2008 22 abc ----------49 Mechanical 03/14/2008 35 xyz -------------if i use group by on some fields then it gives error as followsColumn 'Daily_Time_Entry.daily_type' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.so how can i design query so records display only once not repeated,or is anyalternative,to solve this .my sql query as follows SELECT dl.dt_id, dl.daily_type, convert(char,dl.dt_date,101) as dates,dl.emp_mgmt_id, emp.emp_name,emp.employee_id,dl.project_type,dl.project_id, dl.cec_job_id, cec.cecjobname,dl.time_st,dl.time_ot,dl.time_dt,op.other_proj_id, op.customer_name,op.project_name,op.owner_rep_phone_num1,dl.work_desc, m.material_id,m.material_type,m.material_date, m.project_type ,m.project_id, m.qty,m.description,m.material_unit_price,m.material_markup, m.material_subtotal, m.cec_job_id,m.locationFROM material m left outer join Other_Project op on m.project_id=op.other_proj_id left outer join Daily_Time_Entry dl on dl.project_id =op.other_proj_id inner join Employee_Mgmt emp on emp.emp_mgmt_id = dl.emp_mgmt_id inner join CEC_Job cec ON dl.cec_job_id = cec.cec_job_id where (dl.dt_date='3/14/2008'and m.material_date='3/14/2008') and (dl.project_type='Other Project' and m.project_type = 'Other Project') and (dl.cec_job_id=m.cec_job_id) and (dl.daily_type='Electrical') and (dl.project_id='18')uday |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-03-31 : 08:15:47
|
| If you use group by, you need to state all fields that are in the select statement that are not in a aggregate function.As you have only given 5 columns in your example, I can not say for sure that all records are the same, but am guessing subtotal is different at least and therefore should be summed. |
 |
|
|
udaymahajan
Starting Member
17 Posts |
Posted - 2008-03-31 : 08:59:13
|
quote:
Originally posted by RickD
If you use group by, you need to state all fields that are in the select statement that are not in a aggregate function.As you have only given 5 columns in your example, I can not say for sure that all records are the same, but am guessing subtotal is different at least and therefore should be summed.
thanks for to give response , but for this query any different solutionso that all records display only once,if possible then plz send meuday |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-03-31 : 09:28:43
|
All records probably are displayed only once. If you post examples of what you are getting, then people would be able to help you, but without the examples, there is not a lot we can do.You give this:----------49 Mechanical 03/14/2008 35 xyz -------------48 Electrical 03/14/2008 22 abc ----------49 Mechanical 03/14/2008 35 xyz ------------- But how do we know that this is the same?Your query mentions subtotal, so how do we know the records aren't like this?----------49 Mechanical 03/14/2008 35 xyz 24.99-------------48 Electrical 03/14/2008 22 abc 30.00----------49 Mechanical 03/14/2008 35 xyz 49.99------------- Here, the records are unique. |
|
|
|
|
|
|
|
|
how to display records only once using groupby in