4D v14

Utilizar la cláusula GROUP BY

Inicio

 
Inicio
4D v14
Manual de SQL
Tutorial
Utilizar la cláusula GROUP BY

Utilizar la cláusula GROUP BY  


 

 

Queremos obtener información sobre el número anual total de entradas vendidas desde 1979. El resultado se ordenará por año.

  • El código 4D inicial sería:

      ` 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
      ` Inicializa el resto de las columnas del list box para mostrar la información
     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
      ` Inicializa el resto de las columnas del list box para mostrar la información
     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
      ` Inicializa el resto de las columnas del list box para mostrar la información
     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 el 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
      ` Inicializa el resto de las columnas del list box para mostrar la información
     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 el 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
      ` Inicializa el resto de las columnas del list box para mostrar la información
     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 probar todos los ejemplos anteriores, lance la base "4D SQL Code Samples" y vaya a la ventana principal. A continuación, puede elegir el modo de búsqueda y presionar el botón GROUP BY clause.

 
PROPIEDADES 

Producto: 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)