GROUP BY  SQL. 18     
 


   GROUP BY, HAVING   SQL  ,      .  18    :   ,  , , COUNT DISTINCT,  ,   CASE,   ,   ,     .     SQLite,     .





 

GROUP BY  SQL. 18     





  





    


GROUP BY  ,        .   ,         .

        : HAVING, CASE, COUNT DISTINCT, , CTE    .

     ,           .




   


     :   , , , ,  -   .     .

     ,    .       :     ,  .

    SQLite.  PostgreSQL, MySQL  ClickHouse    ,       .




  


1.     (#task-1)

2.     (#task-2)

3.     (#task-3)

4.    HAVING (#litres_trial_promo)

5.     (#litres_trial_promo)

6.    (#litres_trial_promo)

7.     (#litres_trial_promo)

8.   CASE (#litres_trial_promo)

9.     (#litres_trial_promo)

10.    (#litres_trial_promo)

11.     (#litres_trial_promo)

12.      (#litres_trial_promo)

13.    email (#litres_trial_promo)

14.   (#litres_trial_promo)

15.     (#litres_trial_promo)

16.    (#litres_trial_promo)

17.    (#litres_trial_promo)

18.     (#litres_trial_promo)




 





 1.    



 

 ,      ,     ,    .




CREATE TABLE orders(id INTEGER, status TEXT);

INSERT INTO orders VALUES

(1,'paid'),(2,'new'),(3,'paid'),(4,'cancelled'),(5,'new'),(6,'paid');




SELECT status, COUNT(*) AS orders_count

FROM orders

GROUP BY status

ORDER BY status;


 

[["cancelled", 1], ["new", 2], ["paid", 3]]


 

GROUP BY status        .

COUNT(*)      .

ORDER BY    ,     .


  

  GROUP BY, COUNT(*)      ,     .


 

   paid-  ,   paid   4.

:     paid   cancelled=1, new=2, paid=4.




 2.    



 

   .  ,       .




CREATE TABLE sales(id INTEGER, category TEXT, revenue INTEGER);

INSERT INTO sales VALUES

(1,'books',300),(2,'courses',900),(3,'books',250),(4,'templates',420),(5,'courses',300);




SELECT category, SUM(revenue) AS revenue

FROM sales

GROUP BY category

ORDER BY revenue DESC, category;


 

[["courses", 1200], ["books", 550], ["templates", 420]]


 

SUM  revenue    .

  revenue DESC     .

   category      .


  

     :   ,   .


 

  templates  900.    ?

: templates    1320,   420 + 900  1200.




 3.    



 

      ,      .




CREATE TABLE orders(client TEXT, total INTEGER);

INSERT INTO orders VALUES

('',1000),('',500),('',900),('',300),('',900);




SELECT client, ROUND(AVG(total), 2) AS avg_total

FROM orders

GROUP BY client

ORDER BY avg_total DESC, client;


 

[["", 900.0], ["", 750.0], ["", 600.0]]


 

AVG    total   .




  .


   .

   ,     (https://www.litres.ru/book/raznoe/group-by-v-sql-18-zadach-dlia-otchiotov-i-analitiki-74418883/)  .

      Visa, MasterCard, Maestro,    ,   ,     ,  PayPal, WebMoney, ., QIWI ,       .


