All geek questions in one place

How to accurately use aggregate functions when querying multiple tables?

I'm having a harder time than I would expect to write a query that includes aggregated functions, querying a few tables and giving exact numbers and hoping that I can get some help.

SQL Fiddle

Category table example

A category is what I need to report:

|----|-----------|
| id | name      |
|----|-----------|
| 1  | furniture |
| 2  | music     |
| 3  | kitchen   |
| 4  | adventure |
|----|-----------|

Product table example

Product table example:

|-----|----------------|-------------|
| id  | name           | category_id |
|-----|----------------|-------------|
| 101 | couch          | 1           |
| 102 | chair          | 1           |
| 103 | drum           | 2           |
| 104 | flute          | 2           |
| 105 | pot            | 3           |
| 106 | pan            | 3           |
| 107 | kitchen sink   | 3           |
| 108 | unicorn saddle | 4           |
| 109 | unicorn shoes  | 4           |
| 110 | horse shampoo  | 4           |
|-----|----------------|-------------|

Example Action Table

The data of the representations that we want to summarize (by category) found in the action table:

|----|------------|-------|
| id | product_id | views |
|----|------------|-------|
| 1  | 101        | 1000  |
| 2  | 102        | 2000  |
| 3  | 103        | 3000  |
| 4  | 104        | 4000  |
| 5  | 105        | 5000  |
| 6  | 106        | 6000  |
| 7  | 107        | 7000  |
| 8  | 108        | 8000  |
| 9  | 109        | 9000  |
| 10 | 110        | 10000 |
|----|------------|-------|

Sales table example

, ( ). , vendor_id , . ( ).

|----|------------|-----------|--------|
| id | product_id | vendor_id | amount |
|----|------------|-----------|--------|
| 1  | 101        | 1         | 1000   |
| 2  | 102        | 1         | 900    |
| 3  | 103        | 1         | 2000   |
| 4  | 105        | 1         | 3000   |
| 5  | 107        | 1         | 5000   |
| 6  | 101        | 2         | 600    |
| 7  | 103        | 2         | 7000   |
| 8  | 105        | 2         | 8000   |
| 9  | 107        | 2         | 1000   |
| 10 | 108        | 1         | 500    |
| 11 | 109        | 1         | 600    |
| 12 | 108        | 2         | 400    |
| 13 | 109        | 2         | 500    |
|----|------------|-----------|--------|

:

** , - , , ... , , , ( , ). , , , . , **.

|-----------|----------------|-----------|---------------|-------------------------------|-------------------------|
| category  | count_products | sum_views | average_sales | sum_views_where_sales_=>_1000 | sum_views_sales_<_1000  |
|-----------|----------------|-----------|---------------|-------------------------------|-------------------------|
| adventure | 3              | 27000     | 500           | 0                             | 27000                   |
| furniture | 2              | 3000      | 833           | 0                             | 3000                    |
| kitchen   | 3              | 18000     | 3000          | 6000                          | 12000                   |
| music     | 2              | 7000      | 5000          | 7000                          | 0                       |
|-----------|----------------|-----------|---------------|-------------------------------|-------------------------|

, :

SELECT cat.name AS category,
        count(distinct p.name) AS product,
        sum(a.views) AS views
    FROM
        category AS cat,
        product AS p,
        activity AS a
    WHERE
        cat.id=p.category_id
    AND
        p.id=a.product_id
    GROUP BY 
        category;

sidenote: , . .

:

|-----------|---------|-------|
| category  | product | views |
|-----------|---------|-------|
| Adventure | 3       | 27000 |
| Furniture | 2       | 3000  |
| Kitchen   | 3       | 18000 |
| Music     | 2       | 7000  |
|-----------|---------|-------|

, :

SELECT cat.name AS category,
        count(distinct p.name) AS product,
        sum(a.views) AS views,
        round(avg(s.amount)) AS sales_amount
    FROM
        category AS cat,
        product AS p,
        activity AS a,
        sales AS s
    WHERE
        cat.id=p.category_id
    AND
        p.id=a.product_id
    AND
        p.id=s.product_id
    AND 
        s.vendor_id=1
    GROUP BY 
        category;


|-----------|---------|-------|------------------|
| category  | product | views | avg_sales_amount |
|-----------|---------|-------|------------------|
| Adventure | 2       | 17000 | 550              |
| Furniture | 2       | 3000  | 950              |
| Kitchen   | 2       | 12000 | 4000             |
| Music     | 1       | 3000  | 2000             |
|-----------|---------|-------|------------------|

, , vendor_id, . , , , s.vendor_id = 1. , , .

LEFT JOIN, , , , - ?

+6
3

. , , , , .

, ( ).

, , . . . http://sqlfiddle.com/#!9/02f4b6/31/0

                   SELECT c.id category_id, SUM(a.views) views
                     FROM activity a
                     JOIN product p ON a.product_id = p.id
                     JOIN category c ON p.category_id = c.id
                    GROUP BY c.id

. . http://sqlfiddle.com/#!9/02f4b6/32/0

                   SELECT c.id category_id, 
                          SUM(s.amount) total_sales, 
                          AVG(s.amount) avg_sales
                     FROM sales s
                     JOIN product p ON s.product_id = p.id
                     JOIN category c ON p.category_id = c.id
                    GROUP BY c.id

. , . http://sqlfiddle.com/#!9/02f4b6/42/0

                   SELECT c.id category_id, 
                          COUNT(*) products 
                     FROM product p 
                     JOIN category c ON p.category_id = c.id
                    GROUP BY c.id

. category LEFT JOIN , . http://sqlfiddle.com/#!9/02f4b6/51/0

SELECT c.name, aggproducts.products,
       aggviews.views, aggsales.avg_sales, 
       aggsales.total_sales
  FROM category c
  LEFT JOIN (
                   SELECT c.id category_id, SUM(a.views) views
                     FROM activity a
                     JOIN product p ON a.product_id = p.id
                     JOIN category c ON p.category_id = c.id
                    GROUP BY c.id
       ) aggviews ON c.id = aggviews.category_id
  LEFT JOIN (
                   SELECT c.id category_id, 
                          SUM(s.amount) total_sales, 
                          AVG(s.amount) avg_sales
                     FROM sales s
                     JOIN product p ON s.product_id = p.id
                     JOIN category c ON p.category_id = c.id
                    GROUP BY c.id
       ) aggsales ON c.id = aggsales.category_id
  LEFT JOIN (
                   SELECT c.id category_id, 
                          COUNT(*) products 
                     FROM product p 
                     JOIN category c ON p.category_id = c.id
                    GROUP BY c.id
       ) aggproducts ON c.id = aggproducts.category_id

, , . - , - JOIN.

LEFT JOIN . JOIN, , - .

, , . - , .

. . .

+3

, ...

SELECT Category.name AS category,
       COUNT( * ) AS count_Product,
       SUM( views ) AS sum_views,
       ROUND( COALESCE( SUM( sumAmount ) / SUM( countAmounts ), 0 ) ) AS average_sales,
       SUM( whereGreater ) AS 'sum_views_where_sales_=>_1000',
       SUM( whereLesser ) AS 'sum_views_sales_<_1000'
FROM Category
JOIN Product ON Category.id = Product.category_id
JOIN Activity ON Product.id = Activity.product_id
LEFT JOIN ( SELECT product_id AS product_id,
                   SUM( amount ) AS sumAmount,
                   COUNT( * ) AS countAmounts
            FROM Sales
            GROUP BY product_id
     ) sumCountAmountFinder ON Product.id = sumCountAmountFinder.product_id
JOIN ( SELECT Activity.product_id AS product_id,
              SUM( CASE WHEN COALESCE( meanAmount, 0 ) >= 1000 THEN views ELSE 0 END ) AS whereGreater,
              SUM( CASE WHEN COALESCE( meanAmount, 0 ) < 1000 THEN views ELSE 0 END ) AS whereLesser
       FROM Activity
       LEFT JOIN ( SELECT product_id AS product_id,
                          SUM( amount ) / COUNT( * ) AS meanAmount
                   FROM Sales
                   GROUP BY product_id
                 ) AS meanAmountFinder ON Activity.product_id = meanAmountFinder.product_id
       GROUP BY Activity.product_id
     ) sumWhereFinder ON Product.id = sumWhereFinder.product_id
GROUP BY Category.name;

Category Product, .

Product Activity.

My statement INNER JOIN Category Product, Products, Category.

INNER JOIN Activity , views Product.

amount product_id IN Sales product_id . LEFT JOIN Product , Product . INNER JOIN A LEFT JOIN, Product Sales, , - Product - .

SELECT product_id AS product_id,
       SUM( amount ) AS sumAmount,
       COUNT( * ) AS countAmounts
FROM Sales
GROUP BY product_id

amount product_id Sales ( ).

SELECT product_id AS product_id,
       SUM( amount ) / COUNT( * ) AS meanAmount
FROM Sales
GROUP BY product_id

A LEFT JOIN Activity . amount product_id 1000 product_id views , 0 . product_id Sales, 0 . ( ) .

SELECT Activity.product_id AS product_id,
       SUM( CASE WHEN COALESCE( meanAmount, 0 ) >= 1000 THEN views ELSE 0 END ) AS whereGreater,
       SUM( CASE WHEN COALESCE( meanAmount, 0 ) < 1000 THEN views ELSE 0 END ) AS whereLesser
FROM Activity
LEFT JOIN ( SELECT product_id AS product_id,
                   SUM( amount ) / COUNT( * ) AS meanAmount
            FROM Sales
            GROUP BY product_id
          ) AS meanAmountFinder ON Activity.product_id = meanAmountFinder.product_id
GROUP BY Activity.product_id

, , Category.name. Category.name .

, ...

CREATE TABLE Category
(
    id     INT,
    name   VARCHAR( 50 )
);
INSERT INTO Category ( id,
                       name )
VALUES ( 1, 'furniture' ),
       ( 2, 'music' ),
       ( 3, 'kitchen' ),
       ( 4, 'adventure' );
CREATE TABLE Product
(
    id            INT,
    name          VARCHAR( 50 ),
    category_id   INT
);
INSERT INTO Product ( id,
                      name,
                      category_id )
VALUES ( 101, 'couch',          1 ),
       ( 102, 'chair',          1 ),
       ( 103, 'drum',           2 ),
       ( 104, 'flute',          2 ),
       ( 105, 'pot',            3 ),
       ( 106, 'pan',            3 ),
       ( 107, 'kitchen sink',   3 ),
       ( 108, 'unicorn saddle', 4 ),
       ( 109, 'unicorn shoes',  4 ),
       ( 110, 'horse shampoo',  4 );
CREATE TABLE Activity
(
    id           INT,
    product_id   INT,
    views        INT
);
INSERT INTO Activity ( id,
                       product_id,
                       views )
VALUES ( 1,  101, 1000  ),
       ( 2,  102, 2000  ),
       ( 3,  103, 3000  ),
       ( 4,  104, 4000  ),
       ( 5,  105, 5000  ),
       ( 6,  106, 6000  ),
       ( 7,  107, 7000  ),
       ( 8,  108, 8000  ),
       ( 9,  109, 9000  ),
       ( 10, 110, 10000 );
CREATE TABLE Sales
(
    id           INT,
    product_id   INT,
    vendor_id    INT,
    amount       INT
);
INSERT INTO Sales ( id,
                    product_id,
                    vendor_id,
                    amount )
VALUES ( 1,  101, 1, 1000 ),
       ( 2,  102, 1, 900  ),
       ( 3,  103, 1, 2000 ),
       ( 4,  105, 1, 3000 ),
       ( 5,  107, 1, 5000 ),
       ( 6,  101, 2, 600  ),
       ( 7,  103, 2, 7000 ),
       ( 8,  105, 2, 8000 ),
       ( 9,  107, 2, 1000 ),
       ( 10, 108, 1, 500  ),
       ( 11, 109, 1, 600  ),
       ( 12, 108, 2, 400  ),
       ( 13, 109, 2, 500 );

- , , .

https://dev.mysql.com/doc/refman/5.7/en/case.html ( MySQL CASE)

https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_coalesce ( MySQL COALESCE())

https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_count ( MySQL COUNT())

https://www.w3schools.com/sql/sql_join.asp ( JOIN SQL)

https://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_round ( MySQL ROUND())

https://dev.mysql.com/doc/refman/5.7/en/select.html ( MySQL SELECT)

https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_sum ( MySQL SUM())

, ...

SELECT Category.name AS category,
       COUNT( * ) AS count_Product,
       COALESCE( SUM( views ), '' ) AS sum_views,
       COALESCE( ROUND( SUM( sumAmount ) / SUM( countAmounts ), '' ) ) AS average_sales,
       COALESCE( SUM( whereGreater ), '' ) AS 'sum_views_where_sales_=>_1000',
       COALESCE( SUM( whereLesser ), '' ) AS 'sum_views_sales_<_1000'
FROM Category
LEFT JOIN Product ON Category.id = Product.category_id
LEFT JOIN Activity ON Product.id = Activity.product_id
LEFT JOIN ( SELECT product_id AS product_id,
                   SUM( amount ) AS sumAmount,
                   COUNT( * ) AS countAmounts
            FROM Sales
            GROUP BY product_id
     ) sumCountAmountFinder ON Product.id = sumCountAmountFinder.product_id
LEFT JOIN ( SELECT Activity.product_id AS product_id,
                   SUM( CASE WHEN COALESCE( meanAmount, 0 ) >= 1000 THEN views ELSE 0 END ) AS whereGreater,
                   SUM( CASE WHEN COALESCE( meanAmount, 0 ) < 1000 THEN views ELSE 0 END ) AS whereLesser
            FROM Activity
            LEFT JOIN ( SELECT product_id AS product_id,
                               SUM( amount ) / COUNT( * ) AS meanAmount
                        FROM Sales
                        GROUP BY product_id
                      ) AS meanAmountFinder ON Activity.product_id = meanAmountFinder.product_id
            GROUP BY Activity.product_id
          ) sumWhereFinder ON Product.id = sumWhereFinder.product_id
GROUP BY Category.name;
+1

, .

, , :

SELECT 
    ( `cat`.`name` ) AS `category`,
    COUNT( `p`.`name` ) AS `productsInGroup`,
    SUM( `a`.`views` ) AS `viewsOnGroup`,
    SUM( `s`.`amount` ) / SUM( `salesCnt` ) AS `average_sales`,
    IF( SUM(`s`.`amount`) / SUM( `salesCnt` ) >= 1000, SUM( `a`.`views` ) - SUM( IF(`s`.`salesCnt` IS NULL, `a`.`views`, 0 ) ), 0 ) AS `sum_views_where_sales_>=_1000`,
    IF( SUM(`s`.`amount`) / SUM( `salesCnt` ) < 1000, SUM( `a`.`views` ) , SUM( IF(`s`.`salesCnt` IS NULL, `a`.`views`, 0 ) ) )  AS `sum_views_where_sales_<_1000`
FROM
    `product` AS `p`

INNER JOIN
    `category` AS `cat`
    ON `cat`.`id` = `p`.`category_id`
LEFT JOIN
    `activity` AS `a`
    ON `a`.`product_id` = `p`.`id`
LEFT JOIN(
  SELECT 
  `product_id`,
  COUNT( `product_id` ) AS `salesCnt`,
  SUM( `amount` ) AS `amount`
  FROM `sales`
  GROUP BY `product_id`
) AS `s`
ON `s`.`product_id` = `a`.`product_id`

GROUP BY
    `category`;

, , save calculate .

http://sqlfiddle.com/#!9/02f4b6/144

0

Source: https://habr.com/ru/post/1017199/

More articles:


All Articles