How to sum multiple rows with specific criteria in SQL

Question

I have a query to sum balances by Product below:

SELECT BCM_CORPORATION_NUMBER, BCM_PRODUCT
  SUM(BCM_TOT_NEW_BAL) AS TOTAL_BALANCE
FROM BCM_IN
GROUP BY BCM_CORPORATION_NUMBER, BCM_PRODUCT
ORDER BY BCM_CORPORATION_NUMBER ASC;

Results:

Corp.        PRODUCT.    TOTAL_BALANCE
002030.      VCLBUS.       3000
002030.      VGOBUS.       1000
002030.      VCL000.       4000

I want to combine the totals for Products VCLBUS & VGOBUS to display a total of 4000 together without the VCL000 product.

To look like this: Corp. PRODUCT. TOTAL_BALANCE 002030. VCLBUS/VGOBUS. 4000 002030. VCL000. 4000

where to display the vclbus + vgobus? in separate column?

Ed Bangga
– Ed Bangga

2019-10-01 02:09:18 +00:00
Commented Oct 1, 2019 at 2:09 — Ed Bangga
– Ed Bangga, Commented Oct 1, 2019 at 2:09
Yes that would be great

Richard Coates IV
– Richard Coates IV

2019-10-01 02:10:05 +00:00
Commented Oct 1, 2019 at 2:10 — Richard Coates IV
– Richard Coates IV, Commented Oct 1, 2019 at 2:10
please show the expected result that you want

Squirrel
– Squirrel

2019-10-01 03:46:50 +00:00
Commented Oct 1, 2019 at 3:46 — Squirrel
– Squirrel, Commented Oct 1, 2019 at 3:46

Gordon Linoff · Accepted Answer · 2019-10-01 02:14:39Z

1

Is this what you want?

SELECT BCM_CORPORATION_NUMBER, SUM(BCM_TOT_NEW_BAL) AS TOTAL_BALANCE
FROM BCM_IN
WHERE BCM_PRODUCT IN ('VCLBUS', 'VGOBUS')
GROUP BY BCM_CORPORATION_NUMBER
ORDER BY BCM_CORPORATION_NUMBER ASC;

Or you may what:

WHERE BCM_PRODUCT <> 'VCL000'

answered Oct 1, 2019 at 2:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ed Bangga · Accepted Answer · 2019-10-01 02:48:35Z

0

Here's your query.

SELECT t1.BCM_CORPORATION_NUMBER, t1.BCM_PRODUCT, SUM(t1.TOTAL_BALANCE) 
FROM (SELECT BCM_CORPORATION_NUMBER, BCM_PRODUCT
     SUM(BCM_TOT_NEW_BAL) AS TOTAL_BALANCE, 
     FROM BCM_IN
     GROUP BY BCM_CORPORATION_NUMBER, BCM_PRODUCT
     ) t1
WHERE t1.BCM_PRODUCT IN ('VCLBUS', 'VGOBUS')
ORDER BY t1.BCM_CORPORATION_NUMBER ASC

or

SELECT BCM_CORPORATION_NUMBER
    , BCM_PRODUCT
    , BCM_TOT_NEW_BAL
    , SUM(BCM_TOT_NEW_BAL) AS TOTAL_BALANCE
FROM BCM_IN
WHERE BCM_PRODUCT IN ('VCLBUS', 'VGOBUS')
GROUP BY BCM_CORPORATION_NUMBER, BCM_PRODUCT, BCM_TOT_NEW_BAL
ORDER BY BCM_CORPORATION_NUMBER ASC;

edited Oct 1, 2019 at 2:48

answered Oct 1, 2019 at 2:12

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

2 Comments

Richard Coates IV Over a year ago

So you did a Self Join on table query? I'm assuming WHERE T.1 BCM_PRODUCT IN ('VCLBUS', 'VGOBUS')

Richard Coates IV Over a year ago

In the second query how can I combine the total balance as a separate column?

KumarHarsh · Accepted Answer · 2019-10-01 08:34:44Z

Suppose your Output is in Temp table or CTE

create table #Output(Corp varchar(15),PRODUCT varchar(50),TOTAL_BALANCE int)



insert into #Output 
SELECT BCM_CORPORATION_NUMBER, BCM_PRODUCT
  SUM(BCM_TOT_NEW_BAL) AS TOTAL_BALANCE
FROM BCM_IN
GROUP BY BCM_CORPORATION_NUMBER, BCM_PRODUCT
;

OR

;With OutPutCTE
(
SELECT BCM_CORPORATION_NUMBER, BCM_PRODUCT
  SUM(BCM_TOT_NEW_BAL) AS TOTAL_BALANCE
FROM BCM_IN
GROUP BY BCM_CORPORATION_NUMBER, BCM_PRODUCT
--ORDER BY BCM_CORPORATION_NUMBER ASC
)

Suppose, like you want to group 'VCLBUS', 'VGOBUS' Similarly you will want to Sum GROUP other Product and SUM them accordingly.

Create #temp table and store product according to their group

create table #GroupType(PRODUCT varchar(50),groupid int)
insert into #GroupType values
 ('VCLBUS.',1)
,('VGOBUS.',1)
,('VCL000.',2)

select o.* 
,sum(TOTAL_BALANCE)over(partition by g.groupid )Group_TOTAL_BALANCE 
from #Output O
inner join #GroupType G on o.PRODUCT=g.PRODUCT

I think you can get the whatever output you want.

Collectives™ on Stack Overflow

How to sum multiple rows with specific criteria in SQL

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related