Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Group By error (with Subquery)

Posted on 2012-03-14
3
Medium Priority
?
161 Views
Last Modified: 2012-06-27
Experts,
Thank you for taking my question
Objective: I want to return one line grouped by Property (P.scode).
I have specific code (attached) that needs an expert's review.
I'm getting a common error that I can not resolve:  "Column table.feild is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."  I can not seem to resolve, probably due to a subquery and group by function.

Attached find a word document with two T-SQL:
-Group by Tenant - Successful query grouped by a more detailed "Tenant" level
-Group by Property - represents modification to the success query; resulting with the group by error.
I've tried but failed solve.  If this question looks familar, it is directly related to an open Crystal question.  The Crystal Experts help suggests I attempt fix via T-SQL command (which makes perfect sense, execpt I'm failing to solve vial SQL too)

Data set consists of Many Tenants that belong to a single Property.  

Thank you
ExpertExchange-GroupBy-Property.docx
0
Comment
Question by:LFreehauf
  • 2
3 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 37722661
In that construction  charge.hmy must be one of the columns making part of the group by, don't think that will cause a problem

Group By  p.sCode, p.sAddr1, charge.hmy

But see also you use the same subselect  from table detail twice. It will be better to make it an extra left join (and then no need to have charge.hmy in group by).
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 400 total points
ID: 37723577
I think he's probably right.  You used charge.hmy in the subquery, but not in the GROUP BY.

 I wonder if you could just use SUM on the subqueries, instead of changing the GROUP BY?  For example

, Sum(charge.sTotalAmount) -
    Sum (select isnull(sum(d.samount),0) from detail d
    where d.hchkorchg = charge.hmy and d.cashpost <= '02/29/2012')


 Don't know that I've ever tried anything like that.  Seems like it might work.

 James
0
 
LVL 25

Accepted Solution

by:
jogos earned 1600 total points
ID: 37723679
Select  p.sCode
, p.sAddr1

, Sum(charge.sTotalAmount)
, Sum(charge.sTotalAmount) - isnull(sum(d.samount),0)  -- from join
   
, case when Sum(charge.sTotalAmount) <> 0 then
      (Sum(charge.sTotalAmount) - isnull(sum(d.samount),0) )  -- from join
      / Sum(charge.sTotalAmount) * 100
      else 0 end AS sortCol
From  Property p  Inner Join Tenant t ON p.hMy = t.hProperty  
Inner Join trans charge ON t.hMyPerson = charge.hPerson  
Inner Join acct ON acct.hMy = charge.hOffsetAcct  
Left Outer Join chargtyp ON charge.hRetentionAcct = chargtyp.hMy
-- Introduced left join here
LEFT JOIN  detail d
    ON d.hchkorchg = charge.hmy and d.cashpost <= '02/29/2012'
inner join tenstatus ts on ( ts.istatus = t.istatus )
Where      7 = charge.iType
and chargtyp.sCode='rnt'
and P.scode = 103 and charge.uPostDate ='02/01/2012'
Group By  p.sCode, p.sAddr1
Order By  sortCol desc  -- changed 5 by column alias
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
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…
Suggested Courses

564 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