Group By Clause Problem with MAX

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)
LVL 1
MayoorPatelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
MayoorPatelAuthor Commented:
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
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

MayoorPatelAuthor Commented:
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
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
MayoorPatelAuthor Commented:
Why not?

I only want to group on the columsn i have specified! Why cant i just have the others select as normal?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
MohanKNairCommented:
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
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
@MohanKNair: We are with MSSQL, aren't we?
0
Christopher KileCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.