HAVING  SQL. 18   
 


   HAVING  SQL     .  18    :  ,  , , ,  , , ,     .     SQLite,     .





 

HAVING  SQL. 18   





  





 HAVING    WHERE


WHERE     ,  HAVING    .

     ,         ,    GROUP BY  HAVING.

     ,    : , ,  ,     .




   


  -    : , , ,   .

 ,     : COUNT, SUM, AVG  COUNT DISTINCT.

      SQLite,             .




  


1.        (#task-1)

2.      1000  (#task-2)

3.       (#task-3)

4.       (#litres_trial_promo)

5.      (#litres_trial_promo)

6.      50  (#litres_trial_promo)

7.       (#litres_trial_promo)

8.      (#litres_trial_promo)

9.      (#litres_trial_promo)

10.      (#litres_trial_promo)

11.        (#litres_trial_promo)

12.      (#litres_trial_promo)

13.      (#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(category TEXT, order_id INTEGER, revenue INTEGER);

INSERT INTO orders VALUES

('books',1,300),('books',2,500),('courses',3,1200),('templates',4,200),('templates',5,250);




SELECT category, COUNT(*) AS orders_count

FROM orders

GROUP BY category

HAVING COUNT(*) >= 2

ORDER BY category;


 

[["books", 2], ["templates", 2]]


 

HAVING    ,   COUNT(*) >= 2   GROUP BY.

     ,           .

ORDER BY      :        .


  

  COUNT(*)  WHERE, SQLite  :     ,   .


 

  ,      ?

:     ,        .




 2.      1000 



 

   ,          .




CREATE TABLE sales(seller TEXT, revenue INTEGER);

INSERT INTO sales VALUES

('',400),('',700),('',900),('',300),('',500);




SELECT seller, SUM(revenue) AS total_revenue

FROM sales

GROUP BY seller

HAVING SUM(revenue) >= 1000

ORDER BY seller;


 

[["", 1100]]


 

SUM(revenue)    ,  HAVING      .

     ,           .

ORDER BY      :        .


  

    WHERE revenue >= 1000    ,       .


 

     ?  ,     .

:      900 ,     1000 .




 3.      



 

  ,      400 ,       .




CREATE TABLE order_items(sku TEXT, total INTEGER);

INSERT INTO order_items VALUES

('A1',300),('A1',500),('B2',900),('C3',150),('C3',250);




SELECT sku, ROUND(AVG(total), 1) AS avg_total

FROM order_items

GROUP BY sku

HAVING AVG(total) > 400

ORDER BY sku;


 

[["B2", 900.0]]


 

AVG(total)     SKU,  ROUND      .

     ,           .




  .


   .

   ,     (https://www.litres.ru/book/raznoe/having-v-sql-18-zadach-dlia-otchiotov-74419824/)  .

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


