Problem with SQL group by and summing totals with a calculated column and sub query

David Filler Published at Dev

10

David Filler

I have this query that is working but I'm having trouble with the next step. It calls three different tables and returns a column of account numbers and a cost. I would like to sum the cost but grouped by account numbers. If I were starting out with just a two column table this is trivial. I'm running into issues doing it with a sub query for various reasons. Any input is apprecieated.

select top 100 tblPart.Account,(tblPartLocation.OnHand * tblPart.CostAverage)
as TotalCost 
from tblPartLocation
join tblPart on tblPart.Part = tblPartLocation.Part

Account  Cost
accnt1   3.56  
accnt1   4.78 
annct2   5.00
accnt1   1.23
accnt4   0.01
accnt5   1.01
accnt3   7.45

The expected output is

Account  Cost
accnt1   9.57  
accnt2   5.00 
annct3   7.45
accnt4   0.01
accnt5   1.01

Ed Bangga

you can use sql sum() function then aggregate by Account.

select tblPart.Account, sum(tblPartLocation.OnHand * tblPart.CostAverage) as TotalCost 
from tblPartLocation
join tblPart on tblPart.Part = tblPartLocation.Part
group by tblPart.Account

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2022-08-22

Comments

0 comments

Login to comment

Related

Calculate Sub Query Column Based On Calculated Column

How to GROUP BY a calculated column in a query?

Showing Sub totals in SQL Server query results

recursive sql query with sub running totals

Adding Calculated Columns to a SQL Group By query

SQL Server calculated column using group by

SQL Group By on calculated column - invalid identifier

SQL Summing Group in a Group

Query with Rolling Sub Totals and Percentages

Group by totals as other column values using access sql

Group with summing in SQL Oracle

Grouping Totals With SQL Query

Nested table, within-column-sub-group totals, frequencies and percentages using r "tables" package

SQL query not returning expected values when summing total values in a column

group by along with totals in the same query

SQL Query Summing In Join

SQL Query summing columns

SSRS Hiding the Group Column but not the Totals

Summing column that is grouped - SQL

How to use calculated column value to another column in the same SQL Query

To make a new column from calculated column select in SQL Query

SQL Server Detail Query with totals and grand totals

T-SQL Group By and Sub Query

Using sub query in in group by sql server

How to do GROUP BY on Sub Selected Column in SQL

How to make sql query for adding calculated values group by id?

Summing totals from nested and unnested data in a single query

SQL Create a column with conditions from a sub query

How to query the percent of totals in a group by query

TOP Ranking

Article

HotTag

Archive