 SQL. 18   COUNT, SUM  AVG
 


     SQL     .  18    : COUNT, SUM, AVG, MIN, MAX, COUNT DISTINCT, HAVING,  ,  , weighted average, NULL     KPI-.     SQLite,     .





 

 SQL. 18   COUNT, SUM  AVG





  





  ,  


COUNT, SUM  AVG  ,        ,        .

         :  ,  ,   ,      .

     :    ,   ,   DISTINCT      .




  


     -  .   ,     SQL-,     .

  NULL. COUNT(*), COUNT(column), SUM  AVG     -,     .

    SQLite.   SQL     ,       SQLite.




  


1.    (#task-1)

2.     (#task-2)

3.     (#task-3)

4.      (#litres_trial_promo)

5.  COUNT(*)  COUNT(email) (#litres_trial_promo)

6.    (#litres_trial_promo)

7.     (#litres_trial_promo)

8.     (#litres_trial_promo)

9.      (#litres_trial_promo)

10.      500 (#litres_trial_promo)

11.     (#litres_trial_promo)

12.     (#litres_trial_promo)

13.     (#litres_trial_promo)

14.  SUM  NULL (#litres_trial_promo)

15.     (#litres_trial_promo)

16.     (#litres_trial_promo)

17.      (#litres_trial_promo)

18.   KPI- (#litres_trial_promo)




 





 1.   



 

      ,       .




CREATE TABLE orders(id INTEGER, status TEXT, total INTEGER);

INSERT INTO orders VALUES (1,'paid',500),(2,'new',700),(3,'paid',300),(4,'cancelled',900);




SELECT COUNT(*) AS orders_count

FROM orders;


 

[[4]]


 

COUNT(*)   .

  orders  .

         WHERE.


  

COUNT(*)       .       .


 

  ,      status = test?

: COUNT(*)  5,    WHERE    .




 2.    



 

      ,     .




CREATE TABLE orders(id INTEGER, status TEXT, total INTEGER);

INSERT INTO orders VALUES (1,'paid',500),(2,'new',700),(3,'paid',300),(4,'cancelled',900);




SELECT SUM(total) AS paid_revenue

FROM orders

WHERE status = 'paid';


 

[[800]]


 

WHERE    paid.

SUM(total)  500  300.

new  cancelled     .


  

  WHERE,     ,     .


 

 paid-  200.   paid_revenue?

: paid_revenue  1000,    800  200.




 3.    



 

     ,      .




CREATE TABLE orders(id INTEGER, status TEXT, total INTEGER);

INSERT INTO orders VALUES (1,'paid',500),(2,'new',900),(3,'paid',700),(4,'cancelled',300);




SELECT ROUND(AVG(total), 1) AS avg_paid_total

FROM orders

WHERE status = 'paid';


 

[[600.0]]




  .


   .

   ,     (https://www.litres.ru/book/raznoe/agregaty-sql-18-zadach-na-count-sum-i-avg-74419334/)  .

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


