Solved

SQL sum INNER JOIN column

Posted on 2014-11-14
7
228 Views
Last Modified: 2014-11-19
Consider the following:

tblEntity
entityid int pk
entityname varchar
userid varchar

tblProducts
productid int pk
productDesc varchar
productPrice decimal

tblEntityProducts
epid int pk
entityid int fk
productid int fk

Open in new window


tblEntity
1 Famco bill
2 HatWagons joe
3 Stinkco bill

tblProducts
1 Corn 3.00
2 Hamster 4.00
3 Microscope 10.00

tblEntityProducts
1 1 1
2 1 2
3 2 2
4 2 3
5 3 3

Open in new window


Based on input of userid bill, I am looking for a query to get expected result of

Famco bill 7.00
Stinkco bill 10.00

Open in new window


I can results of
Famco bill 3.00
Famco bill 4.00
Stinkco bill 10.00

Open in new window


Using code

SELECT     e.entityid, e.userid, p.productPrice, z.productid
FROM         tblEntity AS e INNER JOIN
                      tblEntityProducts AS z ON z.entityid = e.entityid INNER JOIN
                      tblProducts AS p ON z.productid = p.productid
WHERE     (e.userid = 'bill')

Open in new window


But if I try to add any GROUP BY or SUM functions, it throws "Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Any ideas?  Can provide further info if needed.  Thank you
0
Comment
Question by:alright
[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
7 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 310 total points
ID: 40444047
Don't include the other columns. If you want to SUM the price by entityName, only include those two columns:


SELECT  e.entityname, SUM(p.ProductPrice) AS TotalPrice
FROM       tblEntity e 
		INNER JOIN tblEntityProducts z ON z.entityid = e.entityid 
                INNER JOIN tblProducts p ON z.productid = p.productid
WHERE     (e.userid = 'bill')
GROUP BY e.entityname 

Open in new window

0
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 95 total points
ID: 40444467
As you also want to see the userid:
SELECT  e.entityID, e.userid, SUM(p.ProductPrice) AS TotalPrice
FROM       tblEntity e 
		INNER JOIN tblEntityProducts z ON z.entityid = e.entityid 
                INNER JOIN tblProducts p ON z.productid = p.productid
WHERE     (e.userid = 'bill')
GROUP BY e.entityID, e.userid

Open in new window

But using entityname instead of entitiyID, as _agx_ did, sounds more reasonable.
0
 
LVL 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 95 total points
ID: 40446765
For what you want the following query is the solution:
SELECT  e.entityname, e.userid, SUM(p.ProductPrice) AS TotalPrice
FROM       tblEntity e 
		INNER JOIN tblEntityProducts z ON z.entityid = e.entityid 
                INNER JOIN tblProducts p ON z.productid = p.productid
WHERE     (e.userid = 'bill')
GROUP BY e.entityname, e.userid

Open in new window

The explanation for that error is in SQL Server if you want to use an aggregate function (MAX, MIN, SUM, COUNT) you will always need to create a GROUP BY clause with the rest of the fields that your SELECT is returning with the exception of the aggregate functions.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Closing Comment

by:alright
ID: 40451192
Thank you for the assistance and the explanation.  Makes perfect sense now
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40451259
You did take notice of the fact that _agx_'s suggestion does not return the desired result as requested?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40451357
@Qlemo - Hah, you're right. All this time I thought the sample data value "Famco bill" was the entityName - not entityName + userID.  I couldn't figure out why you added userID to your query ;-)
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40451941
Yes, that kind of display is obfuscating.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
EditableGrid how to fetch rows from MySql in php 14 47
SQL Rewrite without the NULLIF 4 25
SQL Pivot table 2 45
Estimating my database size 7 17
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 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