How to get count for different values in different columns in SQL

Question

I have a chart table like below

+----------------------------------------------------------+
|  ID | REGION | STATUS | CLOSED_DT |HEALTH  | Open_DT     |
+----------------------------------------------------------+
|   1 | QA     | open   |  -        |  red   | 2016-01-03  |
|   2 | QA     | open   |  -        | green  | 2015-03-03  |
|   3 | QA     | open   |  -        | orange | 2016-02-03  |
|   4 | QA     | open   |  -        | red    | 2016-01-03  |
|   5 | x      | closed |  16-03-03 | green  | 2015-06-03  |
|   6 | Req    | open   |  -        | orange | 2015-05-03  |
|   7 | x      | closed |  16-02-02 | green  | 2015-05-03  |
+----------------------------------------------------------+

I want to display three charts - bar chart , pie chart , month wise trend chart. First chart requires count of different region for open status.

select region, count (*) as total from chart where STATUS='open' group by region;

I used the above query for bar chart. For pie chart i used the below query

select health, count (*) as total from chart where STATUS='open' group by health;

Next is month wise chart where i need to display for the last twelve month For eg from Jan to Dec , i need number of open and closed for each month. How to frame the query to get the count of open and closed status for last twelve months from current date. Also instead of executing three different queries for each chart , is there any way to use a single query. I need query for both Oracle and Mssql

You should tag a question with the database you are actually using. Why do you need both Oracle and SQL Server? — Gordon Linoff
– Gordon Linoff, Commented Mar 20, 2016 at 13:30
Actually i am using oracle and mssql . I need to display charts for data that is in oracle as well as mssql. Thats why tagged both. For 2 systems i need to display the charts — TechEnthu
– TechEnthu, Commented Mar 20, 2016 at 13:34

Gordon Linoff · Accepted Answer · 2016-03-20 13:38:02Z

2

You should use conditional aggregation:

SELECT to_char(Open_DT,'YYYY') as yearCol, to_char(Open_DT,'MM') as MonthCol,
       count(CASE WHEN status = 'open' then 1 end) as openCnt,
       count(CASE WHEN status = 'closed' then 1 end) as closeCnt
FROM YourTable
WHERE Open_DT >= add_months(sysdate,-13) 
GROUP BY to_char(Open_DT,'YYYY'),to_char(Open_DT,'MM')

You tagged both sql-server and oracle, the above solution is for oracle

For sql server :

SELECT year(Open_DT) as yearCol, month(Open_DT) as MonthCol,
       count(CASE WHEN status = 'open' then 1 end) as openCnt,
       count(CASE WHEN status = 'closed' then 1 end) as closeCnt
FROM YourTable
WHERE Open_DT > DATEADD(month,-13,getdate())
GROUP BY year(Open_DT),month(Open_DT)

edited Mar 20, 2016 at 13:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

answered Mar 20, 2016 at 13:31

sagi

40.6k7 gold badges66 silver badges87 bronze badges

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

5 Comments

TechEnthu Over a year ago

How to get last 13 month from today and then use it in the query

TechEnthu Over a year ago

Also for each chart having a separate query is better or a single query?

sagi Over a year ago

Depends on a lot of things, but yes I believe a separate query for each chart is better. @hemamalini

TechEnthu Over a year ago

thanks a lot . Above query works . But suppose if i did not have any data for a month (within 13 months) For example, in Dec 2015 I did not have any incidents open and closed I should get like for Dec 2015 both open and closed count as zero . But above query i am not getting the Dec month itself

TechEnthu Over a year ago

Is there any way to get that data?

Collectives™ on Stack Overflow

How to get count for different values in different columns in SQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related