Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Group By Clause Problem with MAX

Posted on 2007-04-04
10
Medium Priority
?
180 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 71

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 71

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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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 71

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 71

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 71

Expert Comment

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

Accepted Solution

by:
Christopher Kile earned 2000 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

670 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