How to add columns to a view in mysql?

Question

How to add columns to a view in mysql?

Navigation

#1 by (0 votes)

1

I want to make a view contain about 9 columns, so far it's just simple with a single column

What happens is that I have qualification_podcast and qualification_articles and I have them in different views, but I would like to join them in that same one to only separate it by columns and not by views

My code is as follows

CREATE  VIEW 'calificacion_videos2'  AS  select (sum('calificacion'.'calificacion') / count(0)) AS 'cal_video_ovni' from 'calificacion' where ('calificacion'.'Tipo' = 'video' and 'calificacion'.'categoria' = 'Ovni') ;

That only brings me a single column, I would like to know how to make my view contain several columns.

This is the table where I do the view and I'm based on the type of attribute it is and the category of that attribute and depending on the category and the attribute is how each different column should be made.

Example one column for murderers another for creppys and one for legends as well until completing the 9 columns of the view

mysql vistas

asked by David 25.08.2016 в 03:06

source

1 answer

Reduce spacing Thumbnails, page-header in bootstrap Css Can exceptions be made for brew doctor warning messages?

score 0 · Accepted Answer

If what you want is that your view has several independent columns, you can do it in the following way:

CREATE VIEW miVista AS SELECT
    (SELECT .... FROM ....) AS columna1,
    (SELECT .... FROM ....) AS columna2,
    ...
    (SELECT .... FROM ....) AS columnaN;

In your case, it would be something like this:

CREATE  VIEW 'calificacion_videos2' AS  SELECT
   (SELECT (sum('calificacion'.'calificacion') / count(0)) from 'calificacion' where ('calificacion'.'Tipo' = 'video' and 'calificacion'.'categoria' = 'Ovni')) AS cal_video_ovni,
   (SELECT (sum('calificacion'.'calificacion') / count(0)) from 'calificacion' where ('calificacion'.'Tipo' = 'video' and 'calificacion'.'categoria' = 'Misterio')) AS cal_video_misterio,
   (SELECT (sum('calificacion'.'calificacion') / count(0)) from 'calificacion' where ('calificacion'.'Tipo' = 'video' and 'calificacion'.'categoria' = 'Leyendas')) AS cal_video_leyendas;

That will create a view with a single row and three columns (one for UFO, another for Mystery and another for Legends) with the calculations for each of them. You just have to extend it to the rest of the categories you have:

cal_video_ovni  | cal_video_misterio  | cal_video_leyendas
----------------+---------------------+--------------------
6.3             | 6.2                 | 7.3

But I would almost tell you that if you want to get the rating by category, maybe it would be better to change how the view is created so that it makes the calculations for all hit categories (using GROUP BY ), the only thing is that instead of being 1 row with 9 columns, there would be 9 rows with 2 columns (category and calculated value):

categoria       | calificacion
----------------+---------------------
Ovni            | 6.3                 
Misterio        | 6.2
Leyendas        | 7.3

It would be something like this (I have not tried it and it may contain some error):

CREATE  VIEW 'calificacion_videos2' AS  
SELECT   categoria, (sum('calificacion'.'calificacion') / count(0)) 
FROM     'calificacion' 
WHERE    'calificacion'.'Tipo' = 'video' 
GROUP BY 'calificacion'.'categoria'