Select Multiple Fields Group by and Sum

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



Leave a reply



Submit