4D v14

Utilizar a cláusula GROUP BY

Página Inicial

 
Página Inicial
4D v14
Manual de SQL
Tutorial
Utilizar a cláusula GROUP BY

Utilizar a cláusula GROUP BY  


 

 

Queremos obter informação sobre o número anual total de entradas vendidas desde 1979. O resultado será ordenado por ano.

  • O código 4D inicial seria:

      ` Using standard 4D code
     ARRAY INTEGER(aMovieYear;0)
     ARRAY LONGINT(aSoldTickets;0)
     C_LONGINT($MovieYear;$vCrtMovieYear;$i)
     
     REDUCE SELECTION([MOVIES];0)
     $MovieYear:=1979
     QUERY([MOVIES];[MOVIES]Year_of_Movie>=$MovieYear)
     ORDER BY([MOVIES];[MOVIES]Year_of_Movie;>)
     $vCrtMovieYear:=0
     $vInd:=Size of array(aMovieYear)
     For($i;1;Records in selection([MOVIES]))
        If([MOVIES]Year_of_Movie#$vCrtMovieYear)
           $vCrtMovieYear:=[MOVIES]Year_of_Movie
           $vInd:=$vInd+1
           INSERT IN ARRAY(aMovieYear;$vInd;1)
           aMovieYear{$vInd}:=$vCrtMovieYear
           INSERT IN ARRAY(aSoldTickets;$vInd;1)
        End if
        aSoldTickets{$vInd}:=aSoldTickets{$vInd}+[MOVIES]Sold_Tickets
        NEXT RECORD([MOVIES])
     End for
      ` Inicia o resto das colunas do list box para mostrar a informação
     ARRAY TEXT(aTitles;Size of array(aMovieYear))
     ARRAY TEXT(aDirectors;Size of array(aMovieYear))
     ARRAY TEXT(aMedias;Size of array(aMovieYear))
     ARRAY LONGINT(aNrActors;Size of array(aMovieYear))
  • Utilizando código SQL:

      ` Using 4D SQL
     ARRAY INTEGER(aMovieYear;0)
     ARRAY LONGINT(aSoldTickets;0)
     C_LONGINT($MovieYear)
     
     REDUCE SELECTION([MOVIES];0)
     $MovieYear:=1979
     Begin SQL
        SELECT Year_of_Movie, SUM(Sold_Tickets)
        FROM MOVIES
        WHERE Year_of_Movie >= :$MovieYear
        GROUP BY Year_of_Movie
        ORDER BY 1
        INTO :aMovieYear,  :aSoldTickets;
     End SQL
      ` Inicia o resto das colunas do list box para mostrar a informação
     ARRAY TEXT(aTitles;Size of array(aMovieYear))
     ARRAY TEXT(aDirectors;Size of array(aMovieYear))
     ARRAY TEXT(aMedias;Size of array(aMovieYear))
     ARRAY LONGINT(aNrActors;Size of array(aMovieYear))
  • Utilizando comandos SQL genéricos:

      ` Using ODBC commands
     C_TEXT($tQueryTxt)
     ARRAY LONGINT(aSoldTickets;0)
     ARRAY INTEGER(aMovieYear;0)
     C_LONGINT($MovieYear)
     
     REDUCE SELECTION([MOVIES];0)
     $MovieYear:=1979
     SQL LOGIN(SQL_INTERNAL;"";"")
     $tQueryTxt:=""
     $tQueryTxt:=$tQueryTxt+"SELECT Year_of_Movie, SUM(Sold_Tickets)"
     $tQueryTxt:=$tQueryTxt+" FROM MOVIES"
     $tQueryTxt:=$tQueryTxt+" WHERE Year_of_Movie >= :$MovieYear"
     $tQueryTxt:=$tQueryTxt+" GROUP BY Year_of_Movie"
     $tQueryTxt:=$tQueryTxt+" ORDER BY 1"
     SQL EXECUTE($tQueryTxt;aMovieYear;aSoldTickets)
     SQL LOAD RECORD(SQL all records)
     SQL LOGOUT
      ` Inicia o resto das colunas do list box para mostrar a informação
     ARRAY TEXT(aTitles;Size of array(aMovieYear))
     ARRAY TEXT(aDirectors;Size of array(aMovieYear))
     ARRAY TEXT(aMedias;Size of array(aMovieYear))
     ARRAY LONGINT(aNrActors;Size of array(aMovieYear))
  • Utilizando o comando QUERY BY SQL:

      ` Using QUERY BY SQL
     ARRAY LONGINT(aSoldTickets;0)
     ARRAY INTEGER(aMovieYear;0)
     C_LONGINT($MovieYear)
     
     REDUCE SELECTION([MOVIES];0)
     $MovieYear:=1979
     QUERY BY SQL([MOVIES];"Year_of_Movie >= :$MovieYear")
     ORDER BY([MOVIES];[MOVIES]Year_of_Movie;>)
     $vCrtMovieYear:=0
     $vInd:=Size of array(aMovieYear)
     For($i;1;Records in selection([MOVIES]))
        If([MOVIES]Year_of_Movie#$vCrtMovieYear)
           $vCrtMovieYear:=[MOVIES]Year_of_Movie
           $vInd:=$vInd+1
           INSERT IN ARRAY(aMovieYear;$vInd;1)
           aMovieYear{$vInd}:=$vCrtMovieYear
           INSERT IN ARRAY(aSoldTickets;$vInd;1)
        End if
        aSoldTickets{$vInd}:=aSoldTickets{$vInd}+[MOVIES]Sold_Tickets
        NEXT RECORD([MOVIES])
     End for
      ` Inicia o resto das colunas do list box para mostrar a informação
     ARRAY TEXT(aTitles;Size of array(aMovieYear))
     ARRAY TEXT(aDirectors;Size of array(aMovieYear))
     ARRAY TEXT(aMedias;Size of array(aMovieYear))
     ARRAY LONGINT(aNrActors;Size of array(aMovieYear))
  • Utilizando o comando SQL EXECUTE IMMEDIATE:

      ` Using dynamic SQL by EXECUTE IMMEDIATE
     C_TEXT($tQueryTxt)
     ARRAY LONGINT(aSoldTickets;0)
     ARRAY INTEGER(aMovieYear;0)
     C_LONGINT($MovieYear)
     
     $MovieYear:=1979
     $tQueryTxt:=""
     $tQueryTxt:=$tQueryTxt+"SELECT Year_of_Movie, SUM(Sold_Tickets)"
     $tQueryTxt:=$tQueryTxt+" FROM MOVIES"
     $tQueryTxt:=$tQueryTxt+" WHERE Year_of_Movie >= :$MovieYear"
     $tQueryTxt:=$tQueryTxt+" GROUP BY Year_of_Movie"
     $tQueryTxt:=$tQueryTxt+" ORDER BY 1"
     $tQueryTxt:=$tQueryTxt+" INTO :aMovieYear, :aSoldTickets;"
     Begin SQL
        EXECUTE IMMEDIATE :$tQueryTxt;
     End SQL
      ` Inicia o resto das colunas do list box para mostrar a informação
     ARRAY TEXT(aTitles;Size of array(aMovieYear))
     ARRAY TEXT(aDirectors;Size of array(aMovieYear))
     ARRAY TEXT(aMedias;Size of array(aMovieYear))
     ARRAY LONGINT(aNrActors;Size of array(aMovieYear))

Para provar todos os exemplos anteriores, lance a base "4D SQL Code Samples" e vá até a janela principal. A continuação, pode escolher o modo de pesquisa e pressionar o botão GROUP BY clause.

 
PROPRIEDADES 

Produto: 4D
Tema: Tutorial

 
ARTICLE USAGE

Manual de SQL ( 4D v11 SQL Release 4 )
Manual de SQL ( 4D v14)
Manual de SQL ( 4D v12.1)
Manual de SQL ( 4D v13.4)
Manual de SQL ( 4D v14 R2)
Manual de SQL ( 4D v14 R3)
Manual de SQL ( 4D v14 R4)