How to sum two column/row value on the basis of id in SQL query

Question

0.00/5 (No votes)

See more:

I want to sum to rows value in query on condition basis
for example

id count
1 125
2 3
3 111
32 169

i want to sum where id=2 and id=32 these two rows value can sum
like
id count
1 125
2 171 (3+169)
3 111

kindly suggest me how can i do this

What I have tried:

SQL

select s.ShowCauseStatusId, s.CountA, case  when s.ShowCauseStatusId =2 or s.ShowCauseStatusId =32 then SUM(CountA)  else  ' '  end as d from ( select

                ipgc.ShowCauseStatusId
               ,count(*) as CountA

                from dbo.MemoIntellectualPropertyGoodsClass mipgc

                left join dbo.IntellectualPropertyGoodsClass ipgc on mipgc.IntellectualPropertyGoodsClassId = ipgc.Id and ipgc.DataEntryStatusId=1

                left join dbo.MemoIntellectualProperty mip on mipgc.MemoProductId = mip.Id and mip.DataEntryStatusId=1

                left join dbo.IntellectualProperty ip on ipgc.IntellectualPropertyId = ip.Id and ip.DataEntryStatusId=1

                left join dbo.Memo m on mip.MemoId = m.Id and m.DataEntryStatusId=1

                left join dbo.GoodsClass gc on ipgc.GoodsClassId = gc.Id and gc.DataEntryStatusId=1

                left join dbo.Corporate c on ip.ProductCorporateId = c.Id and c.DataEntryStatusId=1

                left join dbo.ContactPerson cp on c.AccountManagerId = cp.Id and cp.DataEntryStatusId=1

                left join dbo.StatusCorrespondence sc on ipgc.StatusId = sc.Id and sc.DataEntryStatusId=1

                left join dbo.TrademarkLetterRemark ltr on ipgc.LetterRemarkId = ltr.Id

                where mipgc.DataEntryStatusId = 1

                and ip.IntellectualPropertyTypeId = 4

                and m.IntellectualPropertyCaseTypeId = 1

               
                and (ipgc.StatusId = 9)
					and c.CorporateStatusId=1
			and (ipgc.ShowCauseStatusId in (1,2,3) 
			or ipgc.StatusId=32)
					and c.CorporateStatusId=1
and ipgc.ApplicationNumber is not null
			 group by ipgc.ShowCauseStatusId 
             

				union all
				select

                ipgc.StatusId 
                ,count(*) as Count


                from dbo.MemoIntellectualPropertyGoodsClass mipgc

                left join dbo.IntellectualPropertyGoodsClass ipgc on mipgc.IntellectualPropertyGoodsClassId = ipgc.Id and ipgc.DataEntryStatusId=1

                left join dbo.MemoIntellectualProperty mip on mipgc.MemoProductId = mip.Id and mip.DataEntryStatusId=1

                left join dbo.IntellectualProperty ip on ipgc.IntellectualPropertyId = ip.Id and ip.DataEntryStatusId=1

                left join dbo.Memo m on mip.MemoId = m.Id and m.DataEntryStatusId=1

                left join dbo.GoodsClass gc on ipgc.GoodsClassId = gc.Id and gc.DataEntryStatusId=1

                left join dbo.Corporate c on ip.ProductCorporateId = c.Id and c.DataEntryStatusId=1

                left join dbo.ContactPerson cp on c.AccountManagerId = cp.Id and cp.DataEntryStatusId=1

                left join dbo.StatusCorrespondence sc on ipgc.StatusId = sc.Id and sc.DataEntryStatusId=1

                left join dbo.TrademarkLetterRemark ltr on ipgc.LetterRemarkId = ltr.Id

                where mipgc.DataEntryStatusId = 1

                and ip.IntellectualPropertyTypeId = 4

                and m.IntellectualPropertyCaseTypeId = 1

               
              --  and (ipgc.StatusId = 9)
					and c.CorporateStatusId=1
			and  ipgc.StatusId=32
					and c.CorporateStatusId=1
and ipgc.ApplicationNumber is not null
			
                group by ipgc.StatusId

				 ) as s

Posted 12-Dec-18 20:58pm

Noman Suleman

Updated 13-Dec-18 22:58pm

Herman<T>.Instance

v2

Add a Solution

Comments

Mohibur Rashid 13-Dec-18 22:58pm

Why are you picking 2 and 32? why are you showing 2 as id?

Noman Suleman 13-Dec-18 23:50pm

yes i have replaced 32 with 2 then it works.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

jaket-cp · Accepted Answer · 2018-12-13T03:54:00

Not 100% sure what you are asking for.
But if you are trying to get sum of count for id where the right most number is the same, then here is a way of doing it in tsql.

SQL

with CntAll as (
	select 1 id, 125 cnt
	union select 2, 3
	union select 3, 111
	union select 32, 169
)
select 
	right(id, 1) rightid,
	sum(cnt) SumCnt
from CntAll
group by
	right(id, 1)
;
--what you will get
--rightid	SumCnt
--1	125
--2	172
--3	111

--Also would like to mention 3+169 = 172 and not 171, I assume a typo


--similar to above but not grouping by right(id, 1) but getting it as a sub type query and grouping the rightid instead - maybe faster execution time
with CntAll as (
	select 1 id, 125 cnt
	union select 2, 3
	union select 3, 111
	union select 32, 169
), CntWithRightId as (
	select 
		right(id, 1) rightId,
		id,
		cnt
	from CntAll
)
select 
	rightid,
	sum(cnt) SumCnt
from CntWithRightId
group by
	rightid
;

Hope that helps you out.

LEOH88 · Accepted Answer · 2018-12-13T15:31:00

Solution 2

Try this

SELECT [id], SUM([count]) AS [count]
FROM (
		SELECT (CASE [id] WHEN 32 THEN 2 ELSE [id] END) AS [id], [count]
		FROM [YourTable]) AS result
GROUP BY [id]

Posted 13-Dec-18 15:31pm

LEOH88

Comments

Noman Suleman 13-Dec-18 23:33pm

thanks for answer