Solved

Group By Clause Problem with MAX

Posted on 2007-04-04
10
172 Views
Last Modified: 2010-03-19
Hi there I am problems with the select statement. I have to put MAX in front of all the select fields because if I dont I get.

Msg 8120, Level 16, State 1, Procedure P_SectorSkills_VerifierActivityDetailedTEST, Line 13
Column 'tblUsers.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Is there any way of modifying the query so that I dont have to use MAX because otherwise it just chooses the best one for each field :(
Here is the query,

SELECT
Max(o.Description) As OrgnisationName,
Max(u.Firstname) As FirstName,
Max(u.LastName)As Lastname,
Max(u.SignUpdate) As SignUpDate,
max(dbo.getmonthdate(t.TransDate)),
max(dbo.getmonth(t.TransDate)),
Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS PassportsIssued,
Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) AS PassportVerifications,
Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications

FROM
tblUsers u
INNER JOIN
tblOrganisations o
ON o.OrganisationID = u.VerifierOrganisationID
INNER JOIN tblTransactions t
ON t.PHID = u.PHID


WHERE
dbo.getmonth(t.Transdate) between dbo.getmonth(@startdate) and dbo.getmonth(@enddate)
AND (o.OrganisationID = (CASE WHEN @OrganisationID = 0 THEN o.OrganisationID ELSE @OrganisationID END))
AND (t.PHID = (CASE WHEN @VerifierID = 0 THEN t.PHID ELSE @VerifierID END))
AND u.SchemeID = @SchemeID

GROUP BY
o.Description, dbo.getmonthdate(t.TransDate), dbo.getmonth(t.TransDate)

ORDER BY
o.Description, dbo.getmonth(T.TransDate)
0
Comment
Question by:MayoorPatel
10 Comments
 
LVL 68

Expert Comment

by:Qlemo
ID: 18851273
Simple approach: put everything you want to have as a single row into group by. As is, for descrption, transdate you won't need a max or alike. Do you really want to group by description???
0
 
LVL 1

Author Comment

by:MayoorPatel
ID: 18851295
I only need the fields that ar ein the GROUP BY as the single row elements. ALL the other stuff ineed not be there, however if I remove the MAX part i get errors, can you help me figure out why?
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 18851364
If you don't need those values, why select them?`As far as I understand, your select should be

SELECT
o.Description As OrgnisationName,
dbo.getmonthdate(t.TransDate),
dbo.getmonth(t.TransDate),
Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS PassportsIssued,
Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) AS PassportVerifications,
Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications

FROM
tblUsers u
INNER JOIN
tblOrganisations o
ON o.OrganisationID = u.VerifierOrganisationID
INNER JOIN tblTransactions t
ON t.PHID = u.PHID


WHERE
dbo.getmonth(t.Transdate) between dbo.getmonth(@startdate) and dbo.getmonth(@enddate)
AND (o.OrganisationID = (CASE WHEN @OrganisationID = 0 THEN o.OrganisationID ELSE @OrganisationID END))
AND (t.PHID = (CASE WHEN @VerifierID = 0 THEN t.PHID ELSE @VerifierID END))
AND u.SchemeID = @SchemeID

GROUP BY
o.Description, dbo.getmonthdate(t.TransDate), dbo.getmonth(t.TransDate)

ORDER BY
o.Description, dbo.getmonth(T.TransDate)
0
 
LVL 1

Author Comment

by:MayoorPatel
ID: 18851521
I do need the other elements in there. I only want the items in the groupby clause to be grouped by. The other elements in the select clause need only be selected as normal. However if I try doing

o.Description As OrgnisationName,
u.Firstname As FirstName,
u.LastName As Lastname,
u.SignUpdate  As SignUpDate,
dbo.getmonthdate(t.TransDate),
dbo.getmonth(t.TransDate),
Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS PassportsIssued,
Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) AS PassportVerifications,
Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications

I get this error

Msg 8120, Level 16, State 1, Procedure P_SectorSkills_VerifierActivityDetailedTEST, Line 13
Column 'tblUsers.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

because firstname is not in an aggregate function OR in the groupby clause.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 18851688
There is no sense in selecting columns you don't want to group. Certainly, you have more than one row for each (Descriptiom, monthname, month) group with different firstname, lastname, signupdate. Which one should be chosen for your sum rows?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:MayoorPatel
ID: 18851758
Why not?

I only want to group on the columsn i have specified! Why cant i just have the others select as normal?
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 18852128
Let's assume the table example with columns a and b, and values

a  b
1  a
2  b
3  a
4  b

if you group only for column b, which value of a is the one to display? I.e. what should this result in:

select a, count(b) from table group by b
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 18852592
Use Oracle analytical function for row number and select the first in sorted order

select emp_name, dept_id from (
select emp_name, dept_id, row_number() over(partition by dept_id order by empid) r1 from emp
)
WHERE r1=1
/
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 18852945
@MohanKNair: We are with MSSQL, aren't we?
0
 
LVL 23

Accepted Solution

by:
Christopher Kile earned 500 total points
ID: 18853883
Why are you grouping by Organization and two dates across an entire set of users, then associating those values grouped across the users as values associated with each of those users?  This does not make sense.  If you want organizational totals, get them; if you want user totals, get them; don't make the organizational totals look like user totals.

Your GROUP BY must include every field that is not in an aggregate function.  Why don't you try this, possibly use it as a subquery, and see if it provides the information you actually need.

SELECT
S.OrgnisationName,
u.Firstname As FirstName,
u.LastName As Lastname,
u.SignUpdate As SignUpDate,
S.MonthDateTransDate,
S.MonthTransDate,
S.PassportsIssued,
S.PassportVerifications,
S.SkillVerifications
FROM
tblUsers AS u
INNER JOIN
(
SELECT
o.Description As OrgnisationName,
u.id AS UserID, -- you do have a unique record identifier for this table, yes?
dbo.getmonthdate(t.TransDate) AS MonthDateTransDate,
dbo.getmonth(t.TransDate) AS MonthTransDate,
Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS PassportsIssued,
Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) AS PassportVerifications,
Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications
FROM
tblUsers u
INNER JOIN
tblOrganisations o
ON o.OrganisationID = u.VerifierOrganisationID
INNER JOIN tblTransactions t
ON t.PHID = u.PHID
WHERE
dbo.getmonth(t.Transdate) between dbo.getmonth(@startdate) and dbo.getmonth(@enddate)
AND (o.OrganisationID = (CASE WHEN @OrganisationID = 0 THEN o.OrganisationID ELSE @OrganisationID END))
AND (t.PHID = (CASE WHEN @VerifierID = 0 THEN t.PHID ELSE @VerifierID END))
AND u.SchemeID = @SchemeID
GROUP BY
o.Description, dbo.getmonthdate(t.TransDate), dbo.getmonth(t.TransDate), u.Firstname As FirstName, u.LastName As Lastname, u.SignUpdate As SignUpDate
) AS S
ON
u.id = S.id
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Inner Join Vs SubQueries 9 26
Report Builder 9 31
SQL Date Retrival 7 31
select over clause 1 10
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Viewers will learn how the fundamental information of how to create a table.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now