Sum counts according to status

Transact-SQL
1

Hi,
I am want to sum the counts based on the status from a table. Is there a way to add the sums to the select statement? I have 3 different statuses: 0, 1 and 2. I would like to get sum of counts 1 and sum of counts 0 and 2.

select productCode,COUNT(productCode) As Count,status
from GameBanks
where requestDateTime >= '20191118' and requestDateTime < '20191121'
group by productCode,status
2

hi this is one way of doing it !!!!

please click arrow to the left for drop create data ... 
drop table #GameBanks
go 

create table #GameBanks
(
productCode int ,
status int ,
requestDateTime date
)
go 


insert into #GameBanks select 1,1,'20191119'
insert into #GameBanks select 1,2,'20191120'
insert into #GameBanks select 1,1,'20191119'
insert into #GameBanks select 1,2,'20191120'
insert into #GameBanks select 1,0,'20191120'

insert into #GameBanks select 2,0,'20191119'
insert into #GameBanks select 2,1,'20191119'
insert into #GameBanks select 2,2,'20191120'
insert into #GameBanks select 2,0,'20191120'
insert into #GameBanks select 2,0,'20191120'
insert into #GameBanks select 2,1,'20191119'
insert into #GameBanks select 2,2,'20191119'
insert into #GameBanks select 2,1,'20191120'
insert into #GameBanks select 2,1,'20191120'
insert into #GameBanks select 2,2,'20191120'
go 

select 'sample data',* from #GameBanks
go

image

please click arrow to the left for SQL 
; WITH cte 
     AS (SELECT productcode, 
                status, 
                Count(status) AS cnt 
         FROM   #gamebanks 
         WHERE  requestdatetime >= '20191118' 
                AND requestdatetime < '20191121' 
         GROUP  BY productcode, 
                   status) 
SELECT 'SQL Output', 
       *, 
       Sum(cnt) 
         OVER( 
           partition BY productcode ) AS sumofcounts 
FROM   cte

image

3

Another way of doing !!!!

please click arrow to the left for ANOTHER WAY sql 
; WITH cte 
     AS (SELECT productcode, 
                status, 
                Count(status) AS cnt 
         FROM   #gamebanks 
         WHERE  requestdatetime >= '20191118' 
                AND requestdatetime < '20191121' 
         GROUP  BY productcode, 
                   status), 
     cte_sumcounts 
     AS (SELECT productcode, 
                Count(status) AS cntstat 
         FROM   #gamebanks 
         GROUP  BY productcode) 
SELECT 'SQL OUTPUT', 
       b.*, 
       a.cntstat 
FROM   cte_sumcounts a 
       JOIN cte b 
         ON a.productcode = b.productcode 

go

image

4

Thank you @harishgg1, Is there a way to retrieve data as follows;

productCode, SumOf1s,SumOf0And2s

5

Yes ...

Case statement .

Please give me 40 minutes

I will do

Thanks

6

hi

is this what you are looking for !!!!

please click arrow to the left for SQL 
; WITH cte 
     AS (SELECT productcode, 
                Sum(CASE 
                      WHEN status IN ( 0, 2 ) THEN 1 
                      ELSE 0 
                    END) AS SumOf0And2s, 
                Sum(CASE 
                      WHEN status IN ( 1 ) THEN 1 
                      ELSE 0 
                    END) AS SumOf1s 
         FROM   #gamebanks 
         WHERE  requestdatetime >= '20191118' 
                AND requestdatetime < '20191121' 
         GROUP  BY productcode) 
SELECT 'SQL productCode, SumOf1s,SumOf0And2s', 
       * 
FROM   cte 

go

image

1 Like
7

Splendid @harishgg1, is there anything that you can't do with SQL? :):grinning:

8

You mean

Me personally

Or

SQL in general

9

If you mean me personally

This is very very easy simple stuff
For people who have a lot of experience
Writing SQL
We would have seen a lot of stuff over and over again

If you mean SQL
There is a lot " not possible " in SQL
Requires other things. .software etc

10

I meant you @harishgg1, wish I could do half of you.

11

In this task .even though it is very simple
there is a lot lot lot of information involved...

For someone new ..who has no idea
Will be very very tough

On the other hand
If you know
Learning how to learn. ... very fast
How to break it up and build it up
It's all thoughts ..
What kind of notes to take

Screenshot_2019-11-21-00-21-14-08_c0dc27f5c07cb0fb3541d6073dfd6932
Screenshot_2019-11-21-00-21-14-08_c0dc27f5c07cb0fb3541d6073dfd69321080×2340 396 KB

12

How to learn anything 10x faster seems promising :grinning: thank you.

13

I could give a demo
To show you how
:+1::+1::sunglasses:

14

why not, may be when you have time :slight_smile:

15

I will post it in this thread tomorrow.. or we could take it offline

16

Hi @harishgg1,

What is the difference between your query and this one? They both seem returning the same result.

SELECT productcode, Sum(CASE WHEN status IN ( 0, 2 ) THEN 1 ELSE 0 END) AS 'CANCEL\UNCERTAIN', Sum(CASE WHEN status IN ( 1 ) THEN 1 ELSE 0 END) AS CONFIRM FROM GameConfirmResponses WHERE requestdatetime >= '20191118' AND requestdatetime < '20191121' GROUP BY productcode

17

there is no difference in output

i was doing the earlier output in CTE and modified the CTE

its the same thing -- i just got lazy to remove the CTE

for example ..
what happened

-- earlier SQL ..
; with CTE
(
select top 10 * from table a join table b on a.id =b.id
)
select * from cte

-- what i did was used the CTE for writing different code
; with CTE
(
select col1 from tableXYZ
)
select * from cte

-- this produces the same output as
select col1 from tableXYZ

got lazy to remove the CTE part

hope i am clear what happened !!!
:slight_smile: :slight_smile:

18

:+1:

19

@harishgg1 I have one more question; I have a similar table. Can I merge/sum the results?
Here is the other table query:
SELECT productcode, Sum(CASE WHEN status IN ( 0, 2 ) THEN 1 ELSE 0 END) AS 'CANCEL\UNCERTAIN', Sum(CASE WHEN status IN ( 1 ) THEN 1 ELSE 0 END) AS CONFIRM FROM **GameBanks** WHERE requestdatetime >= '20191118' AND requestdatetime < '20191121' GROUP BY productcode

20

hi Cenk

Please tell me what you mean ??
by merge/sum the results

Please show in diagram what you mean ?? Something very simple to understand !!!
Example

image
image885×278 7.29 KB