Click here to Skip to main content
15,301,414 members
Please Sign up or sign in to vote.
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
Updated 10-Dec-17 19:59pm

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
   
Comments
ahsal 12-Dec-17 2:32am
   
Actually i solved it in the classic way (Result)/2
Select SUM( myTable1.ColumnName ) as ColumnName,myTable1.ID
From myTable1
INNER JOIN myTable2
on myTable1.ID = myTable2.ID
Group by myTable1.ID
   
Comments
ahsal 12-Dec-17 2:32am
   
Actually i solved it in the classic way (Result)/2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900