CODEWITHSUNDEEP
mysql
Rizal Terris Elvalino
Rizal Terris Elvalino

Reputation: 77

Select Count (Date) And Set empty date with zero value

My problem is I cannot count how much data appears in that month and if no data give value 0 as default.

id | data_plan | data_start
---------------------------
1  | 2018-7-29 | 2018-8-29
2  | 2018-7-29 | 2018-9-29
3  | 2018-9-29 | 2018-10-29
4  | 2018-3-29 | 2018-10-29
5  | 2018-7-29 | 2018-9-29
6  | 2018-9-01 | 2018-9-29
7  | 2018-4-29 | 2018-11-29

My results

cplan       | cstart
---------------------------
March       | October
April       | November
July        | August
July,July   | September,September
September   | September
September   | October

Desired results.

monthdata | cplan       | cstart
---------------------------
January   | 0       | 0
February  | 0       | 0
March     | 1       | 0
April     | 1       | 0
May       | 0       | 0
June      | 0       | 0
July      | 3       | 0
Aug       | 0       | 1
Sept      | 2       | 3
October   | 0       | 2
November  | 0       | 1
December  | 0       | 0

This is my query

SELECT 
GROUP_CONCAT(Monthname(data_plan)) as cplan,
GROUP_CONCAT(Monthname(data_start)) as cstart
FROM 
data 
group by data_plan,data_start

My Fiddle

Upvotes: 1

Views: 528

Answers (6)

Esperento57
Esperento57

Reputation: 17472

Solution 3:

WITH months(MonthNumber, Month_Name) AS
(
    SELECT 1, datename(M, '2018-01-01') Month_Name
    UNION ALL
    SELECT MonthNumber+1, datename(M, '2018-' + str(MonthNumber+1) + '-01') Month_Name
    FROM months
    WHERE MonthNumber < 12
),
CountPlan as (
select month(data_plan) MonthNumber, count(*) cplan 
from data 
group by month(data_plan)
),
CountStart as (
select month(data_start) MonthNumber, count(*) cstart 
from data 
group by month(data_start)
)
select f0.Month_Name, 
isnull(f1.cplan, 0) cplan,
isnull(f2.cstart, 0)  cstart
from months f0
left outer join CountPlan f1 on f1.MonthNumber=f0.MonthNumber
left outer join CountStart f2 on f2.MonthNumber=f0.MonthNumber

Upvotes: 0

Esperento57
Esperento57

Reputation: 17472

Solution 2 :

WITH months(MonthNumber, Month_Name) AS
(
    SELECT 1, datename(M, '2018-01-01') Month_Name
    UNION ALL
    SELECT MonthNumber+1, datename(M, '2018-' + str(MonthNumber+1) + '-01') Month_Name
    FROM months
    WHERE MonthNumber < 12
)
select f0.Month_Name, 
(select count(*) cplan from data f1 where month(f1.data_plan)=f0.MonthNumber) cplan,
(select count(*) cstart from data f1 where month(f1.data_start)=f0.MonthNumber) cstart
from months f0

Upvotes: 0

Esperento57
Esperento57

Reputation: 17472

Solution 1 :

WITH months(MonthNumber, Month_Name) AS
(
    SELECT 1, datename(M, '2018-01-01') Month_Name
    UNION ALL
    SELECT MonthNumber+1, datename(M, '2018-' + str(MonthNumber+1) + '-01') Month_Name
    FROM months
    WHERE MonthNumber < 12
)
select f0.Month_Name, f2.cplan, f4.cstart 
from months f0
outer apply
(
select count(*) cplan from data f1
where month(f1.data_plan)=f0.MonthNumber
) f2

outer apply
(
select count(*) cstart from data f3
where month(f3.data_start)=f0.MonthNumber
) f4

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

You could use a subquery for buid a list of month and the left join the data table 

select monthname( t.my_date) monthname
  , ifnull(count(mount(date_plan)),0) cplan
  , ifnull(count(month(date_plan)),0) cstart
from (
  select str_to_date('2018-01-01', '%Y-%m-%d') as my_date 
  union 
  select str_to_date('2018-02-01', '%Y-%m-%d')
  union 
  select str_to_date('2018-03-01', '%Y-%m-%d')
  union 
  select str_to_date('2018-04-01', '%Y-%m-%d') 
  union 
  select str_to_date('2018-05-01', '%Y-%m-%d')
  union 
  select str_to_date('2018-06-01', '%Y-%m-%d')
  union 
  select str_to_date('2018-07-01', '%Y-%m-%d')
  union 
  select str_to_date('2018-08-01', '%Y-%m-%d')
  union 
  select str_to_date('2018-09-01', '%Y-%m-%d')
  union 
  select str_to_date('2018-10-01', '%Y-%m-%d') 
  union 
  select str_to_date('2018-11-01', '%Y-%m-%d')
  union 
  select str_to_date('2018-12-01', '%Y-%m-%d')
) t  
left join  data d  on month(d.date_plan) = month(t.my_date)
group by monthname

Upvotes: 0

SanduniC
SanduniC

Reputation: 41

Use COUNT() function:

SELECT 
    COUNT(Monthname(data_plan)) as cplan,
    COUNT(Monthname(data_start)) as cstart
FROM data
GROUP BY Monthname(data_plan)

Upvotes: 0

Nick
Nick

Reputation: 147166

To get the results you want, you first have to create a table of months, then you have to LEFT JOIN that to tables which contain counts of data_plan and data_start for each month:

SELECT MONTHNAME(CONCAT('2018-', m.month, '-01')) AS month, 
       COALESCE(cplan, 0) AS cplan,
       COALESCE(cstart, 0) AS cstart
FROM (SELECT 1 AS month UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
                        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
                        UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
                        UNION SELECT 11  UNION SELECT 12) m
LEFT JOIN (SELECT MONTH(data_plan) AS month, COUNT(*) AS cplan
           FROM data
           GROUP BY month) dp ON dp.month = m.month
LEFT JOIN (SELECT MONTH(data_start) AS month, COUNT(*) AS cstart
           FROM data
           GROUP BY month) ds ON ds.month = m.month
GROUP BY m.month

Output:

month       cplan   cstart
January     0       0
February    0       0
March       1       0
April       1       0
May         0       0
June        0       0
July        3       0
August      0       1
September   2       3
October     0       2
November    0       1
December    0       0

Updated SQLFiddle

Upvotes: 1

Related Questions