File contents

Group by && Having

1.- Quina és la comanda promig de cada venedor?


 rep |       avg
-----+------------------
 101 |  8876.0000000000
 102 |  5694.0000000000
 103 |  1350.0000000000
 105 |  7865.4000000000
 106 | 16479.0000000000
 107 | 11477.3333333333
 108 |  8376.1428571429
 109 |  3552.5000000000
 110 | 11566.0000000000
(9 rows) 

2.- Quin és el rang de quotes asignades a cada oficina? ( es a dir, el mínim i el màxim)



 oficina_rep |    min    |    max
-------------+-----------+-----------
          11 | 275000.00 | 300000.00
          12 | 200000.00 | 300000.00
          13 | 350000.00 | 350000.00
          21 | 350000.00 | 350000.00
          22 | 300000.00 | 300000.00
        NULL |      NULL |      NULL
(6 rows)


3.- Quants venedors estan asignats a cada oficina?



 oficina_rep | count
-------------+-------
          11 |     2
          12 |     3
          13 |     1
          21 |     2
          22 |     1
        NULL |     1
(6 rows)


4.- Quants clients són atesos per cada cada venedor?


 rep_clie | count 
----------+-------
      110 |     1
      109 |     2
      108 |     2
      107 |     1
      106 |     2
      105 |     2
      104 |     1
      103 |     3
      102 |     4
      101 |     3
(10 rows)


5.- Calcula el total de l'import de les comandes per cada venedor i per cada client.


 rep | clie |   sum
-----+------+----------
 101 | 2102 |  3978.00
 101 | 2108 |   150.00
 101 | 2113 | 22500.00
 102 | 2106 |  4026.00
 102 | 2114 | 15000.00
 102 | 2120 |  3750.00
 103 | 2111 |  2700.00
 105 | 2103 | 35582.00
 105 | 2111 |  3745.00
 106 | 2101 |  1458.00
 106 | 2117 | 31500.00
 107 | 2109 | 31350.00
 107 | 2124 |  3082.00
 108 | 2112 | 47925.00
 108 | 2114 |  7100.00
 108 | 2118 |  3608.00
 109 | 2108 |  7105.00
 110 | 2107 | 23132.00
(18 rows)

6.- El mateix que a la qüestió anterior, però ordenat per client i dintre de client per venedor.


clie | rep |   sum
------+-----+----------
 2101 | 106 |  1458.00
 2102 | 101 |  3978.00
 2103 | 105 | 35582.00
 2106 | 102 |  4026.00
 2107 | 110 | 23132.00
 2108 | 101 |   150.00
 2108 | 109 |  7105.00
 2109 | 107 | 31350.00
 2111 | 103 |  2700.00
 2111 | 105 |  3745.00
 2112 | 108 | 47925.00
 2113 | 101 | 22500.00
 2114 | 102 | 15000.00
 2114 | 108 |  7100.00
 2117 | 106 | 31500.00
 2118 | 108 |  3608.00
 2120 | 102 |  3750.00
 2124 | 107 |  3082.00
(18 rows)

Multitaula
Una limitació de SQL, quan fem consultes agrupades, és que ignora informació referent a claus primarias. La conseqüència és que si volem fer una consulta com aquesta:

"Calcula les comandes totals per a cada venedor" 

i fem

select num_empl, nombre, sum(importe)
from pedidos, repventas
where rep = num_empl
group by num_empl;

Obtenim un missatge del tipus:

ERROR:  Attribute repventas.nombre must be GROUPed or used in an aggregate function

La consulta té sentit, ja que agrupar per "número" d'empleat és, efectivament, el mateix que per "nom" d'empleat. Però, tot i així, SQL informa d'una errada ja que la columna NOMBRE no s'inclou com a columna d'agrupació. Bé, doncs a corretgir:


7.- Calcula les comandes totals per a cada venedor


 num_empl |    nombre     |   sum
----------+---------------+----------
      101 | Dan Roberts   | 26628.00
      102 | Sue Smith     | 22776.00
      103 | Paul Cruz     |  2700.00
      105 | Bill Adams    | 39327.00
      106 | Sam Clark     | 32958.00
      107 | Nancy Angelli | 34432.00
      108 | Larry Fitch   | 58633.00
      109 | Mary Jones    |  7105.00
      110 | Tom Snyder    | 23132.00
(9 rows) 

(HAVING)

8.- Quin és el tamany promig de les comandes per cada venedor, les comandes dels quals sumen més de 30 000?

 rep |       avg
-----+------------------
 108 |  8376.1428571429
 107 | 11477.3333333333
 106 | 16479.0000000000
 105 |  7865.4000000000

(4 rows) 

9.- Per cada oficina amb 2 o més persones, calculeu la quota total i les vendes totals per a tots els venedors que treballen a la oficina (volem que surti la ciutat de l'oficina a la consulta)

   ciudad    |    sum    |    sum
-------------+-----------+-----------
 Chicago     | 775000.00 | 735042.00
 Los Angeles | 700000.00 | 835915.00
 New York    | 575000.00 | 692637.00
(3 rows)


10.- Es desitja un llistat de fabricants productes indicant el seu id i el nombre d'existèncias que hi ha dels seus productes. Només volem tenir en compte els productes de preu superior a 54. Només volem que apareguin els fabricants amb un nombre total d'unitats superior a 200.

 id_fab | sum 
--------+-----
 qsa    | 278
 rei    | 248
 aci    | 843
(3 rows)

(REPAS HAVING + GROUP BY)

11- Mostra la mitjana d'edat i número de treballadors de les oficines ordenades per número de treballadors.

   ciudad    |         avg         | num_treballadors 
-------------+---------------------+------------------
 Denver      | 49.0000000000000000 |                1
 Atlanta     | 37.0000000000000000 |                1
 New York    | 41.5000000000000000 |                2
 Los Angeles | 55.0000000000000000 |                2
 Chicago     | 35.6666666666666667 |                3
(5 rows)



12.- Mostra el preu, les existències i la quantitat total de les comandes de cada producte per als quals la quantitat total demanada està per sobre del 75% de les existències.

  descripcion  | precio  | existencias | sum
---------------+---------+-------------+-----
 Ajustador     |   25.00 |          37 |  30
 Bancada Motor |  243.00 |          15 |  16
 Riostra 1-Tm  | 1425.00 |           5 |  22
 Bisagra Dcha. | 4500.00 |          12 |  15
(4 rows)

13.- Mostra de les comandes el nom del venedor i el nombre de clients que atén. Només volem comptar els clients amb un limit de crèdit superior a 30.000.

    nombre     | rep | count 
---------------+-----+-------
 Dan Roberts   | 101 |     2
 Sue Smith     | 102 |     2
 Paul Cruz     | 103 |     1
 Bill Adams    | 105 |     2
 Sam Clark     | 106 |     2
 Nancy Angelli | 107 |     1
 Larry Fitch   | 108 |     2
 Mary Jones    | 109 |     1
 Tom Snyder    | 110 |     1
(9 rows)

14.- Mostra el número de productes que tenen un preu superior a 148 i dels quals queden un número inferior a 30 existencies.

 count 
-------
    12
(1 row)


15.- Mostra per cada client el número de comandes que ha fet amb més de 10 productes de quantitat. Volem saber el nom de l'empresa i l'import total de les comandes.

     empresa      |   sum    | count 
------------------+----------+-------
 Acme Mfg.        | 34880.00 |     3
 Chen Associates  | 31350.00 |     1
 First Corp.      |  3978.00 |     1
 Fred Lewis Corp. |  1896.00 |     1
 Orion Corp       |  7100.00 |     1
 JCP Inc.         |  4345.00 |     2
(6 rows)

16.- Quins són els 5 productes que han estat venuts a més clients diferents? Mostreu el número de clients per cada producte. A igualtat de nombre de clients es volen ordenats per ordre decreixent d'existències i, a igualtat d'existències, per descripció. Mostreu tots els camps pels quals s'ordena.

 nombre_clients | producto | existencias |    descripcion    
----------------+----------+-------------+-------------------
              3 | xk47     |          38 | Reductor
              2 | 2a45c    |         210 | V Stago Trinquete
              2 | 41002    |         167 | Articulo Tipo 2
              2 | 41004    |         139 | Articulo Tipo 4
              2 | 4100x    |          37 | Ajustador
(5 rows)