CODEWITHSUNDEEP
mysqlview
pette
pette

Reputation: 87

Create a view from 2 tables with multiple subquery

I have two tables

table song

|   ID  |   TITLE   |   AUTOR   |   CODE    |
|   1   |   title_1 |   autor_1 |   123     |
|   2   |   title_2 |   autor_2 |   1332    |
|   3   |   title_3 |   autor_3 |   3434    |

table playlist

|   ID  |   DATE        |   EVENT   |
|   1   |   2020-05-01  |   123     |
|   2   |   2020-05-02  |   3434    |
|   3   |   2020-09-23  |   123     |

now I create a VIEW because table are very large

CREATE OR REPLACE VIEW View_song
AS
select c.id, c.title, c.autor, c.code,
( select
count(p.event) from playlist p
where p.event =  c.code
) as total_counter
from song c

So I have a VIEW with the total songs + the column total_counter


|   ID  |   TITLE   |   AUTOR   |   CODE    |   total_counter   |  
|   1   |   title_1 |   autor_1 |   123     |       2           |
|   2   |   title_2 |   autor_2 |   1332    |       0           |
|   3   |   title_3 |   autor_3 |   3434    |       0           |

I want to add a column to the VIEW so I want to have a more column with today_counter

|   ID  |   TITLE   |   AUTOR   |   CODE    |   total_counter   |  today_counter    |
|   1   |   title_1 |   autor_1 |   123     |       2           |       0           |
|   2   |   title_2 |   autor_2 |   1332    |       0           |       0           |
|   3   |   title_3 |   autor_3 |   3434    |       0           |       1           |

I try this query


CREATE OR REPLACE VIEW View_song
AS
select c.id, c.title, c.autor, c.code,
( select
count(p.event) from playlist p
where p.event =  c.code
) as total_counter
( select
count(p.event) from playlist p
where p.event =  c.code AND date = CURDATE()
) as today_counter
from song c

but it doesn't work

Upvotes: 0

Views: 82

Answers (1)

Dark Knight
Dark Knight

Reputation: 6541

Use GROUP BY to these calculations in single go.

CREATE OR REPLACE VIEW View_song
AS

SELECT 
    c.id, c.title, c.author, c.code, 
    COUNT(p.`event`) AS total_counter,
    SUM(IF(p.`date` = CURDATE(), 1, 0)) AS today_counter
FROM song c
LEFT JOIN playlist p ON c.`code` = p.`event`
GROUP BY c.id;

Add index on date, code and event to make it faster.

Also in your query, you're missing comma after total_counter.

Upvotes: 1

Related Questions