February 19, 2014 at 8:49 am
Hi,
I'm trying to write a query to select various columns from 3 tables. In the where clause I use a set of conditions, but most important condition is that I only want to see all results from the different columns where the ph.ProdHeaderDossierCode contains at least 25 lines of processed hours. I tried this with group by and having, but I constant get error messages on all other columns that I want to see: "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". How can I make this so I can see all information I need? Here is my code so far:
selectph.CalculatedTotalTime,
ph.ProdHeaderDossierCode,
ph.MachGrpCode,
ph.EmpId,
pd.PartCode
fromdbo.T_ProcessedHour ph,
dbo.T_ProductionHeader pd,
dbo.T_DossierMain dm
whereph.ProdHeaderDossierCode = pd.ProdHeaderDossierCode and
pd.DossierCode = dm.DossierCode and
ph.RegDate >= '2013-01-01 00:00:00:000' and
dm.DelDate < getdate() and
ph.MachGrpCode not in ('04A', '14A') and
ph.HourCode in ('01','09','10','11','12')
group by ph.ProdHeaderDossierCode
havingcount(ph.MachGrpCode) >= 25
I would be very thankful if anyone can help me out.
February 19, 2014 at 9:02 am
Without sample data, I guess you need to do something like this:
SELECTph.CalculatedTotalTime,
ph.ProdHeaderDossierCode,
ph.MachGrpCode,
ph.EmpId,
pd.PartCode
FROMdbo.T_ProcessedHour ph
JOINdbo.T_ProductionHeader pd ON ph.ProdHeaderDossierCode = pd.ProdHeaderDossierCode
JOINdbo.T_DossierMain dm ON pd.DossierCode = dm.DossierCode
whereph.RegDate >= '2013-01-01 00:00:00:000' and
dm.DelDate < getdate() and
ph.MachGrpCode not in ('04A', '14A') and
ph.HourCode in ('01','09','10','11','12') and
ph.ProdHeaderDossierCode IN (SELECT ph2.ProdHeaderDossierCode
FROM T_ProcessedHour ph2
GROUP BY ph2.ProdHeaderDossierCode
HAVING count(ph2.MachGrpCode) >= 25)
I changed your JOINS to ANSI-92 JOINS. It's a better practice, especially when you have to combine them with outer joins.
February 19, 2014 at 9:24 am
Columns in the SELECT clause that are not part of the GROUP BY should participate in an aggregation function.
ph.CalculatedTotalTime,
ph.MachGrpCode,
ph.EmpId,
pd.PartCode
Can you post the exact error msg you are getting?
February 19, 2014 at 9:33 am
hunchback (2/19/2014)
Columns in the SELECT clause that are not part of the GROUP BY should participate in an aggregation function.ph.CalculatedTotalTime,
ph.MachGrpCode,
ph.EmpId,
pd.PartCode
Can you post the exact error msg you are getting?
He posted it, is the expected error and you're right on what you say. But to get the expected result, including the columns in the group by or an aggregate function might not work.
February 19, 2014 at 9:37 am
Thanks, Luis!
Didn't notice the OP posted the exact error but I had an idea what the error could be. I was just expalining why he/she is getting the error.
Another solution is using a CTE / derived table and the OVER clause but the semantic could change if we are not clear. Using the OVER clause as in my example will inquire for at least 25 lines after the joins and filters.
with C1 as (
selectph.CalculatedTotalTime,
ph.ProdHeaderDossierCode,
ph.MachGrpCode,
ph.EmpId,
pd.PartCode,
count(ph.MachGrpCode) over(partition by ph.ProdHeaderDossierCode) as cnt
fromdbo.T_ProcessedHour ph,
dbo.T_ProductionHeader pd,
dbo.T_DossierMain dm
whereph.ProdHeaderDossierCode = pd.ProdHeaderDossierCode and
pd.DossierCode = dm.DossierCode and
ph.RegDate >= '2013-01-01 00:00:00:000' and
dm.DelDate < getdate() and
ph.MachGrpCode not in ('04A', '14A') and
ph.HourCode in ('01','09','10','11','12')
)
select *
from C1
where cnt >= 25;
February 19, 2014 at 10:03 am
Many thanks! I tried this and it gives exactly the results I was looking for! I think I now need to put a lot of effort in better learning working with joins and the many possibillities in the where clauses, as I lack knowledge on that...
February 19, 2014 at 10:12 am
It's good to know that it worked for you. Do you understand why does it work and how? Do you have any questions you would like to ask?
For future posts, I suggest you to follow the recommendations described on the article linked in my signature.
February 19, 2014 at 11:24 pm
Based on the solution you provided to me, I've done some reading and trying. I understand there are some benefits in using joins instead of the where clause (like smaller chance of forgetting links between tables, more readable code etc etc). Also using subquery's in the where clause is alittle more clear now. But I need to study and try it more, to see what the possibillities are and how I figure out what to use when. You understand I really am a sql newbie....
Off course read the article about forum etiquette, I will keep that in mind with future posts.
Thanks for your help and comments.
February 20, 2014 at 10:03 pm
It's great to read all that. If you need more help, don't hesitate on coming back. We're here to help and hopefully you'll end up helping once you get used to work with sql server.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply