I need to show a dataset in MYSQL

Question

Need to pull the amount of matches played, amount of goals scored, create an average goal per match (average goals = Number of matches / Number of goals) all in one View,

I did the VIEW but always pulls as if returning more than 1 Row, follows line of programming.

    CREATE VIEW segunda AS
SELECT
    (SELECT count(partidas) FROM standings GROUP BY time_id) as partidas,
    (SELECT count(gols_a_favor) FROM standings GROUP BY time_id) as gols,
    ((SELECT count(partidas) FROM standings GROUP BY time_id) / (SELECT count(gols_a_favor) FROM standings GROUP BY time_id)) as Quantidade_de_gols,
    (SELECT max(gols_a_favor) FROM standings GROUP BY time_id) as time_mais_gols,
    (select max(gols_a_favor) FROM standings GROUP BY gols_a_favor) as gols_do_time_max
FROM
    partidas JOIN times AS primeiro_time JOIN standings AS standings;

and the complete programming:

DROP DATABASE IF EXISTS `senai_copa`;
CREATE DATABASE `senai_copa`; 
USE `senai_copa`;

CREATE TABLE times (
    id INTEGER UNSIGNED auto_increment NOT NULL,
    pais CHARACTER VARYING(64) NOT NULL,
    tecnico CHARACTER VARYING(128) NOT NULL,
    nome_do_time ENUM("A", "B", "C", "D", "E", "F", "G", "H") NOT NULL,
    PRIMARY KEY(id)
) ENGINE=INNODB;

CREATE TABLE jogadores (
    id INTEGER UNSIGNED auto_increment NOT NULL,
    nome CHARACTER VARYING(128) NOT NULL,
    time_id INTEGER UNSIGNED NOT NULL,
    FOREIGN KEY (time_id) REFERENCES times(id),
    PRIMARY KEY (id, time_id)
) ENGINE=INNODB;

CREATE TABLE estadios (
    id INTEGER UNSIGNED auto_increment NOT NULL,
    nome CHARACTER VARYING(32) NOT NULL,
    cidade CHARACTER VARYING(16) NOT NULL,
    publico INTEGER UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE partidas (
    id INTEGER UNSIGNED NOT NULL,
    primeiro_time INTEGER UNSIGNED NULL,
    segundo_time INTEGER UNSIGNED NULL,
    data INTEGER UNSIGNED NOT NULL,
    estadio INTEGER UNSIGNED NOT NULL,
    FOREIGN KEY (estadio) REFERENCES estadios(id),
    FOREIGN KEY (primeiro_time) REFERENCES times(id),
    FOREIGN KEY (segundo_time) REFERENCES times(id),
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE gols (
    id INTEGER UNSIGNED auto_increment NOT NULL,
    jogo_id INTEGER UNSIGNED NOT NULL,
    time_id INTEGER UNSIGNED NOT NULL,
    jogador_id INTEGER UNSIGNED NOT NULL,
    minuto INTEGER UNSIGNED NOT NULL,
    tempo INTEGER UNSIGNED NOT NULL,
    type ENUM("normal", "og", "penalty"),
    FOREIGN KEY (time_id) REFERENCES times(id),
    FOREIGN KEY (jogador_id) REFERENCES jogadores(id),
    FOREIGN KEY (jogo_id) REFERENCES partidas(id),
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE cartaos (
    id INTEGER UNSIGNED auto_increment NOT NULL,
    jogo_id INTEGER UNSIGNED NOT NULL,
    time_id INTEGER UNSIGNED NOT NULL,
    jogador_id INTEGER UNSIGNED NOT NULL,
    minuto INTEGER UNSIGNED NOT NULL,
    tempo INTEGER UNSIGNED NOT NULL,
    cartao ENUM("red", "yellow"),
    FOREIGN KEY (time_id) REFERENCES times(id),
    FOREIGN KEY (jogador_id) REFERENCES jogadores(id),
    FOREIGN KEY (jogo_id) REFERENCES partidas(id),
    PRIMARY KEY (id)
) ENGINE=INNODB;


CREATE VIEW primeira AS
SELECT
    partidas.id,
    partidas.data as data,
    publico,
    cartao as cartões,
    primeiro_time.id as primeiro_time_id,
    primeiro_time.pais as primeiro_time_pais,
    segundo_time.id as segundo_time_id,
    segundo_time.pais as segundo_time_pais,
    (SELECT COUNT(*) FROM gols WHERE time_id = primeiro_time.id AND gols.jogo_id = partidas.id AND (type = "normal" || type = "penalty"))+(SELECT COUNT(*) FROM gols WHERE time_id = segundo_time.id AND gols.jogo_id = partidas.id AND type = "og") AS resultado_primeiro_time,
    (SELECT COUNT(*) FROM gols WHERE time_id = segundo_time.id AND gols.jogo_id = partidas.id AND (type = "normal" || type = "penalty"))+(SELECT COUNT(*) FROM gols WHERE time_id = primeiro_time.id AND gols.jogo_id = partidas.id AND type = "og") AS resultado_segundo_time
FROM
    partidas JOIN times AS primeiro_time ON primeiro_time = primeiro_time.id JOIN times AS segundo_time ON segundo_time = segundo_time.id JOIN cartaos as cartão JOIN estadios as publico;


CREATE TABLE standings (
    time_id INTEGER UNSIGNED,
    partidas TINYINT UNSIGNED DEFAULT 0,
    ganhas TINYINT UNSIGNED DEFAULT 0,
    empate TINYINT UNSIGNED DEFAULT 0,
    perdidas TINYINT UNSIGNED DEFAULT 0,
    gols_a_favor INTEGER  DEFAULT 0,
    gols_Contra INTEGER DEFAULT 0,
    pontos TINYINT UNSIGNED DEFAULT 0,
    PRIMARY KEY(time_id),
    FOREIGN KEY (time_id) REFERENCES times(id)
);

CREATE VIEW segunda AS
SELECT
    (SELECT count(partidas) FROM standings GROUP BY time_id) as partidas,
    (SELECT count(gols_a_favor) FROM standings GROUP BY time_id) as gols,
    ((SELECT count(partidas) FROM standings GROUP BY time_id) / (SELECT count(gols_a_favor) FROM standings GROUP BY time_id)) as Quantidade_de_gols,
    (SELECT max(gols_a_favor) FROM standings GROUP BY time_id) as time_mais_gols,
    (select max(gols_a_favor) FROM standings GROUP BY gols_a_favor) as gols_do_time_max
FROM
    partidas JOIN times AS primeiro_time JOIN standings AS standings;

Always it returns error 1242, subquery Return more than 1 Row

Answer 1

About the View

Need to pull the amount of matches played, amount of goals scored, create an average goal per match (average goals = Number of matches / Number of goals) all in one View

According to your database structure, I believe that the view next return what you need:

create view partidas_gols as
Select
  (select count(*) from partidas) quantidade_de_partidas_jogadas,
  (Select count(*) from gols) quantidade_de_gols_marcados,
  ( (Select count(*) from gols) / (select count(*) from partidas)) as media_de_gols

It’s worth noting that I thought there was a misunderstanding when you said you needed:

create an average goal per match (average goals = Number of matches / Number of goals)

That way I considered the right thing to be: create an average goal per match (average of goals = amount of goals / Amount of pranks )

About the erro 1242, SUBQUERY return more than 1 row

The problem is that in sub-consultations there is no clause that causes the result of the sub-consumption to return only one line.

For example, say the command SELECT * FROM standings return:

time_id	pranks	winnings	draw	lost	gols_a_favor	goals_Contra	dots
1	2	1	0	0	3	2	20
2	2	0	1	1	2	3	10

In the view segunda the subconsultation (SELECT count(partidas) FROM standings GROUP BY time_id) as partidas would return: |Count(departures)| |----------------| |1| |1| As in the example there are two time_id as a result the number of lines equal to the amount of time_id In our example there are two lines. But as this sub-query represents a result column, the Database Manager System (DBMS) expects the sub-query to return only one row, as this is not happening, the DBMS is reporting the error: erro 1242, SUBQUERY return more than 1 row.

To correct:
Based on your command

CREATE VIEW segunda AS
SELECT
   (SELECT count(partidas) FROM standings GROUP BY time_id) as partidas,
   (SELECT count(gols_a_favor) FROM standings GROUP BY time_id) as gols,
   ((SELECT count(partidas) FROM standings GROUP BY time_id) / (SELECT > 
    count(gols_a_favor) FROM standings GROUP BY time_id)) as Quantidade_de_gols,
   (SELECT max(gols_a_favor) FROM standings GROUP BY time_id) as time_mais_gols,
   (select max(gols_a_favor) FROM standings GROUP BY gols_a_favor) as > 
    gols_do_time_max
FROM
   partidas JOIN times AS primeiro_time JOIN standings AS standings;

An option is to relate each Sub-Nsulta to the main query so that the result of the sub-nsulta is unique. Follow an example that makes the query acceptable for the DBMS:

SELECT
    (SELECT count(partidas) FROM standings where time_id = partidas.primeiro_time GROUP BY time_id) as partidas,
    (SELECT count(gols_a_favor) FROM standings where time_id = partidas.primeiro_time GROUP BY time_id) as gols,
    ((SELECT count(partidas) FROM standings  where time_id = partidas.primeiro_time GROUP BY time_id) / (SELECT count(gols_a_favor) FROM standings  where time_id = partidas.primeiro_time  GROUP BY time_id)) as Quantidade_de_gols,
    (SELECT max(gols_a_favor) FROM standings  where time_id = partidas.primeiro_time GROUP BY time_id) as time_mais_gols,
    (select max(gols_a_favor) FROM standings  where time_id = partidas.primeiro_time GROUP BY gols_a_favor) as gols_do_time_max
FROM
    partidas 
   JOIN times AS primeiro_time on primeiro_time.id = partidas.primeiro_time 
   JOIN standings  on primeiro_time.id = standings.time_id ;

Note that the excerpt was included where time_id = partidas.primeiro_time in the Sub-consultations with the aim of having only one line as a result. This above query is only to demonstrate a possible correction of the problem, as the relationships in joins and the correlation between the consulta principal and the subconsultas are at your discretion according to your needs.

Tip

To solve this type of error, try to isolate the sub-queries so that you find the one that is returning more than one line so you can add criteria in the command that make your result in a row as DBMS waits.