CODEWITHSUNDEEP
sqlsql-serveroracle-database
TechEnthu
TechEnthu

Reputation: 97

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

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

Upvotes: 2

Views: 1464

Answers (1)

sagi
sagi

Reputation: 40481

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)

Upvotes: 2

Related Questions