Solved

Group By Clause Problem with MAX

Posted on 2007-04-04
10
174 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 69

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 69

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 69

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 69

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 69

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

776 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