How do we select multiple columns in a sum()/group by query?
try this:
You have to make your first query as a derived table and join with the actual table to get all the fields
SELECT A.[Vendor Name], [Vendor No], [Item No], [Item Description],
[Item Cost], [Quantity],B.Total
from [SalesDB] A
inner join
(SELECT [Vendor Name], SUM([Quantity]) AS Total
FROM [SalesDB]
WHERE [Vendor No] IN (1,2,3,4,5,6,7,8)
AND [Item Description] = 'bolts'
Group By [Vendor Name])B
on A.[Vendor Name]=B.[Vendor Name]
Select multiple fields group by and sum
Updated :
If you're trying to avoid grouping for all the fields, you can group just by Id
:
data.GroupBy(d => d.Id)
.Select(
g => new
{
Key = g.Key,
Value = g.Sum(s => s.Value),
Name = g.First().Name,
Category = g.First().Category
});
But this code assumes that for each Id
, the same Name
and Category
apply. If so, you should consider normalizing as @Aron suggests. It would imply keeping Id
and Value
in one class and moving Name
, Category
(and whichever other fields would be the same for the same Id
) to another class, while also having the Id
for reference. The normalization process reduces data redundancy and dependency.
How to select multiple columns and group by one column
It looks like you want a window sum rather than aggregation.
That is, replace:
SUM(C.amount) as FINAL_AMOUNT
With:
SUM(C.amount) OVER(PARTITION BY A.NUMBER) as FINAL_AMOUNT
Accordingly, you need to remove the GROUP BY
clause from the query.
How to select multiple columns, sum one column and group by multiple columns
are you looking for this? :
select FORMAT(d.date_start, 'yyyy-MM') date_start
, count(distinct user_id) total
from planner d
group by FORMAT(date_start, 'yyyy-MM')
How to SUM columns on multiple conditions in a GROUP BY
Since you didn't tell us what's going wrong (that is, describe the behavior you get in addition to describing the the behavior you expect), it's hard to say where, but there are a couple of possibilities. Neil points out one. Another is that since you join on the transaction table three times, you're pairing transactions with transactions and getting repetitions. Instead, join on the transaction table a single time and change how you sum up the Amount
column.
Select
a.ACCOUNT_ID,
a.BANK_NAME,
a.LOCALE,
a.STATUS,
sum(t.AMOUNT) as BALANCE,
sum((t.AMOUNT < 0) * t.AMOUNT) As OUTGOING,
sum((t.AMOUNT > 0) * t.AMOUNT) As INCOMING
From ACCOUNT a
Left Join TRANSACTION t On t.ACCOUNT_ID = a.ACCOUNT_ID
Group By a.ACCOUNT_ID, a.BANK_NAME, a.LOCALE, a.[STATUS]
You can use CASE
expressions as a more readable alternative to the multiplications:
Select
a.ACCOUNT_ID,
a.BANK_NAME,
a.LOCALE,
a.[STATUS],
sum(t.AMOUNT) As BALANCE,
sum(CASE WHEN t.AMOUNT < 0 THEN t.AMOUNT ELSE 0 end) As OUTCOME,
sum(CASE WHEN t.AMOUNT > 0 THEN t.AMOUNT ELSE 0 end) As INCOME
From ACCOUNT a
Left Join [TRANSACTION] t On t.ACCOUNT_ID = a.ACCOUNT_ID
Group By a.ACCOUNT_ID, a.BANK_NAME, a.LOCALE, a.[STATUS]
SELECT SUM of columns from multiple table and group by id
If you want to put the data "side-by-side", I would suggest union all
and group by
:
select user, date, sum(a_total) as a_total, sum(b_total) as b_total
from ((select user, date, total as a_total, 0 as b_total
from purchase_rec_a
) union all
(select user, date, 0 as a_total, total as b_total
from purchase_rec_b
)
) ab
group by user, user_date;
If you don't want date in the result, you can use the same structure, just changing the outer query:
select user, sum(a_total) as a_total, sum(b_total) as b_total
from ((select user, date, total as a_total, 0 as b_total
from purchase_rec_a
) union all
(select user, date, 0 as a_total, total as b_total
from purchase_rec_b
)
) ab
group by user;
Related Topics
Save Byte[] into a SQL Server Database from C#
How to Count Number of Visitors for Website in ASP.NET C#
What Regular Expression Would I Use to Remove Everything After the Second Backslash
How to Split Json String in C# and Store in Separate Variables
How to Change Cell Background Color in Wpf Datagrid
Convert Json File to C# and Stored in Database
How to Make Xmlserializer Ignore the Namespace on Deserialization
Why Am I Getting Error Cs0246: the Type or Namespace Name Could Not Be Found
Automatically Update Values in Database from Datagridview
Add Header and Footer for Pdf Using Itextsharp
How to Extract Custom Header Value in Web API Message Handler
Sorting a Collection Containing Strings And/Or Numbers
Asp.Net Core Get Json Array Using Iconfiguration
How to Loop Through Each and Every Row, Put Value in Gridview Column Cells
Newtonsoft Json Dynamic Property Name
Check If Datetime Is a Weekend or a Weekday
How to Get an Specific Header Value from the Httpresponsemessage