Use sum in query of 2 tables of 2 databases

Question

0.00/5 (No votes)

See more:

hello,

below is my query, the query result is 2 columns from 2 different databases 
the columns "OriginalPremium" & "Share"

Share is always static in EACH query run but in some queries are 50 some are 60..etc
the question: how to get the SUM of the "OriginalPremium" in my Query result ??


SELECT OriginalPremium, Users.Share
FROM [URSYSTEM].[PolicyTable] a inner join [LoginDB].[ahsal-UR].Users on Producer = LoginDB.[ahsal-UR].Users.Username


Now my output is:
--------------------------
|OriginalPremium | Share |
--------------------------
|      10        |  50   |
--------------------------
|      5         |  50   |
--------------------------
|      15        |  50   |
--------------------------




what i want is:
--------------------------
|OriginalPremium | Share |
       30        |  50   |
--------------------------

 

by the way i used Sum(OriginalPremium) as Total

it shows me this error:

Msg 8120, Level 16, State 1, Line 3
Column 'LoginDB.ahsal-UR.Users.Share' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What I have tried:

by the way i used Sum(OriginalPremium) as Total

it shows me this error:

Msg 8120, Level 16, State 1, Line 3
Column 'LoginDB.ahsal-UR.Users.Share' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Posted 9-Dec-17 3:45am

ahsal

Updated 10-Dec-17 19:59pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-12-09T03:53:00

You can't aggregate values with a GROUP BY and include non-aggregated values unless they are specifically included in the GROUP BY: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]

To do that, you need to create a subquery which does the grouping and aggregate, and then JOIN that to the other table.

SQL

SELECT a.ID, a.Total, b.SalesManName FROM (SELECT ID, SUM(MyColumn) AS Total FROM MyTable GROUP BY ID) a
JOIN myOtherTable b ON a.ID = b.MyTableID

Rahul JR · Answer 2 · 2017-12-10T19:59:00

Solution 2

Select SUM( myTable1.ColumnName ) as ColumnName,myTable1.ID
From myTable1
INNER JOIN myTable2
on myTable1.ID = myTable2.ID
Group by myTable1.ID

Posted 10-Dec-17 19:59pm

Rahul JR

Comments

ahsal 12-Dec-17 2:32am

Actually i solved it in the classic way (Result)/2