CODEWITHSUNDEEP
mysqlsql
The KNVB
The KNVB

Reputation: 3844

How to generate the following statistic from a table?

I have a table named incident_summary that structure and data as following:

month,system_id,s_count

202104,1,50

202104,2,6

202105,1,14

202105,2,4

202106,1,1

202106,2,1

I would like to generate the following statistic:

s_count_on_202106,s_count_before_202106

2,74

where

s_count_on_202106 is sum of s_count value on 202106

s_count_before_202106 is sum of s_count value before 202106

I have tried the following SQL:

select  
  sum(case when month<202106 then s_count else 0 end)
  sum(case when month=202106 then s_count else 0 end)
from incident_summary
group by month

However, it does not work, would you help to me to solve the problem?

Upvotes: 0

Views: 36

Answers (2)

selvarajmas
selvarajmas

Reputation: 1643

Try the following Query. May be it helps you.

SELECT
    t1.s_count_on_202106,
    t2.s_count_before_202106
FROM
(
    SELECT
        sum(s_count) AS s_count_on_202106
    FROM
        incident_summary
    WHERE
        month = 202106
) AS t1,
(
    SELECT
        sum(s_count) AS s_count_before_202106
    FROM
        incident_summary
    WHERE
        month < 202106
) AS t2

Upvotes: 1

Amit Verma
Amit Verma

Reputation: 2490

Sum again on your result set.

SELECT SUM(s_count_before_202106)s_count_before_202106,  SUM(s_count_on_202106)s_count_on_202106
FROM
(
    select  
      sum(case when month<202106 then s_count else 0 end)s_count_before_202106 ,
      sum(case when month=202106 then s_count else 0 end)s_count_on_202106
    from incident_summary
    group by month
)T;

Upvotes: 0

Related Questions