Solved

Group By Clause Problem with MAX

Posted on 2007-04-04
10
179 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 70

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 70

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 70

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
 
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 70

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 70

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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

622 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