CODEWITHSUNDEEP
mysqldatetime
ilham ramadhan
ilham ramadhan

Reputation: 53

Mysql Join date on one table

I have one table named seismik

╔═══════════════╦══════════╗
║      date     ║ type     ║ 
╠═══════════════╬══════════╣
║   2019-04-01  ║  rock    ║ 
║   2019-04-01  ║  water   ║
║   2019-04-01  ║  water   ║
║   2019-04-02  ║  rock    ║
║   2019-04-02  ║  rock    ║
║   2019-04-03  ║  water   ║
║   2019-04-03  ║  water   ║
║   2019-04-04  ║  rock    ║
╚═══════════════╩══════════╝

What I want to do is to count how much type rock in each date, but I want the date which don't have rock still exist and the value is null.

So I use this query 

SELECT date,type, COUNT(`type`) AS `freq`
FROM seismik 
WHERE type = "rock"
GROUP BY date_time

And the result become

╔═══════════════╦══════════╦═════════╗
║      date     ║ type     ║ freq    ║ 
╠═══════════════╬══════════╬═════════╣
║   2019-04-01  ║  rock    ║    1    ║
║   2019-04-02  ║  rock    ║    2    ║
║   2019-04-04  ║  rock    ║    1    ║
╚═══════════════╩══════════╩═════════╝

What I expected is

╔═══════════════╦══════════╦═════════╗
║      date     ║ type     ║ freq    ║ 
╠═══════════════╬══════════╬═════════╣
║   2019-04-01  ║  rock    ║    1    ║
║   2019-04-02  ║  rock    ║    2    ║
║   2019-04-03  ║  null    ║   null  ║ <- This
║   2019-04-04  ║  rock    ║    1    ║
╚═══════════════╩══════════╩═════════╝

Upvotes: 0

Views: 115

Answers (3)

Vcoder
Vcoder

Reputation: 99

select s.Date,e.type,count(e.type)/4 
FROM seismik as s left join seismik as e
on e.type = s.type and e.type='rock'
GROUP BY date;

Result:-

2019-04-01 rock 1.0000

2019-04-02 rock 2.0000

2019-04-04 rock 1.0000

2019-04-03 0.0000

Upvotes: 0

Jonathan Distenfeld
Jonathan Distenfeld

Reputation: 69

In your query, you are selecting all entries, that have the type "rock". If you want also want to get those with type null, you will have to remove your where-clause.

Upvotes: -1

Fahmi
Fahmi

Reputation: 37473

use case when and remove the where condition

DEMO

 SELECT dateval,max(case when types='rock' then types end) types, COUNT(case when `types`='rock' then 1 end) AS `freq`
 FROM t1 
 GROUP BY dateval

OUTPUT:

dateval     types   freq
2019-04-01  rock    1
2019-04-02  rock    2
2019-04-03          0
2019-04-04  rock    1

Upvotes: 3

Related Questions