[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

SQL Server 2005/2008

Here is the sql
Select
A.Field1,A.Field2,
Count(A.Field3),
Sum(B.Field4),
(Select Count(D.Field5) from TableD as D
            Where A.FieldID = D.FieldID),
(Select Sum(D.Field5) from TableD as D
            Where A.FieldID = D.FieldID)
 
from TableA as a
INNER JOIN TableB as B ON A.FieldID = B.FieldID
left outer Join TableC as C ON A.FieldID = C.FieldID
Group by A.Field1,A.Field2
Order by A.Field1

Get an error
Column 'TableD.Field5' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

I can make TableD has left join something like below..
but count/sum duplicating the numbers so i wanted to use subquery in main select sql.

Select
A.Field1,A.Field2,
Count(A.Field3),
Sum(B.Field4),
Count(D.Field5),
Sum(D.Field5)
 
from TableA as a
INNER JOIN TableB as B ON A.FieldID = B.FieldID
left outer Join TableC as C ON A.FieldID = C.FieldID
left outer Join TableD as D ON A.FieldID = D.FieldID
Group by A.Field1,A.Field2
Order by A.Field1

I'm trying to make sure my result out of TableD shouldn't duplicate count/sum.
Select 
A.Field1,A.Field2,
Count(A.Field3),
Sum(B.Field4),
(Select Count(D.Field5) from TableD as D
		Where A.FieldID = D.FieldID),
(Select Sum(D.Field5) from TableD as D
		Where A.FieldID = D.FieldID)
 
from TableA as a 
INNER JOIN TableB as B ON A.FieldID = B.FieldID
left outer Join TableC as C ON A.FieldID = C.FieldID
Group by A.Field1,A.Field2
Order by A.Field1

Get an error
Column 'TableD.Field5' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

Select 
A.Field1,A.Field2,
Count(A.Field3),
Sum(B.Field4),
Count(D.Field5), --count is duplicated
Sum(D.Field5) -- Sum is duplicated.
 
from TableA as a 
INNER JOIN TableB as B ON A.FieldID = B.FieldID
left outer Join TableC as C ON A.FieldID = C.FieldID
left outer Join TableD as D ON A.FieldID = D.FieldID
Group by A.Field1,A.Field2
Order by A.Field1

Count/Sum duplicated because TableA/TableB/TableC has multiple records.

Open in new window

0
onebite2
Asked:
onebite2
  • 3
1 Solution
 
Scott PletcherSenior DBACommented:
Something like below should give you what you want:
Select 
A.Field1,A.Field2,
Count(A.Field3),
Sum(B.Field4),
Max(D.Field5_Count) AS Field5_Count,
Max(D.Field5_SUM) AS Field5_Sum 
from TableA as a 
INNER JOIN TableB as B ON A.FieldID = B.FieldID
left outer Join TableC as C ON A.FieldID = C.FieldID
left outer join (
    Select FieldID, SUM(Field5) AS Field5_SUM, COUNT(Field5) AS Field5_Count
    from TableD
    group by FieldID
) as D ON A.FieldID = D.FieldID

Group by A.Field1,A.Field2
Order by A.Field1

Open in new window

0
 
Scott PletcherSenior DBACommented:
CORRECTION:

Uh oh, thought the Group By and the JOIN values for tableA were the same, but now I see they're not.  Not sure if the sub-query techique will directly help you in that case.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
distinct/group by considerations:
http://www.experts-exchange.com/A_3203.html
0
 
onebite2Author Commented:
Scott,
Just because there is FieldID on the left outer join, my number are wrong and if don't have FieldID on Select query, sql would give an error.

This is what i want, this way i get correct Sum and Count because sql would ignore FieldID.
left outer join (
    Select SUM(Field5) AS Field5_SUM, COUNT(Field5) AS Field5_Count
    from TableD
    group by FieldID
) as D ON A.FieldID = D.FieldID
Rest of the sql is correct, except this left outer join.

0
 
Scott PletcherSenior DBACommented:
We need to GROUP BY the same columns in the sub-query as in the outer query. Like this (should be ... hopefully):

left outer join (
    Select A2.Field1, A2.Field2, 
        SUM(D2.Field5) AS Field5_SUM, COUNT(D2.Field5) AS Field5_Count
    from TableA A2
    inner join TableD D2 ON D2.FieldID = A2.FieldID
    group by A2.Field1, A2.Field2
) as AD_Grp ON A.Field1 = AD_Grp.Field1 AND A.Field2 = AD_Grp.Field2

Open in new window

0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now