Geek Answers Handbook

COUNT (table_name.column_name), which does not give an exact calculation. Am I applying GROUP BY on the wrong column?

I have a somewhat complicated database structure that tracks products. Here is the diagram generated by MySQL Workbench:

Products Diagram

In this structure, I have 3 products that I added. All three of these products have an attribute colorand an option red. I have a sql script created here: http://sqlfiddle.com/#!2/68470/4 displaying a query that I run to try to get a column opt_countto say 3 in the rows where the column attributeis equal colorand the column optionis red.

opt_count , , , .

opt_count ?

+4
4

, , ( ), .

, , . , , (COUNT ( _)), product_id, .

SELECT pvo.option, 
       count(distinct product_id), 
       group_concat(distinct product_id) products
  FROM (`products`)
  JOIN `product_variant_combinations` pvc using(`product_id`)
  JOIN `product_variants` pv using(`combination_id`)
  JOIN `product_variant_ao_relation` pv_ao using(`ao_id`)
  JOIN `product_variant_options` pvo using(`option_id`)
  JOIN `product_variant_attributes` pva using(`attribute_id`)
 group by pvo.option;

:

3 111026,111025,111024

: http://sqlfiddle.com/#!2/68470/133

, :

SELECT pva.attribute, pvo.option, count(distinct product_id), group_concat(product_id)
FROM (`products`)
JOIN `product_variant_combinations` pvc using(`product_id`)
JOIN `product_variants` pv using(`combination_id`)
JOIN `product_variant_ao_relation` pv_ao using(`ao_id`)
JOIN `product_variant_options` pvo using(`option_id`)
JOIN `product_variant_attributes` pva using(`attribute_id`)
group by pva.attribute, option

GROUP BY SELECT. : COUNT GROUP_CONCAT, GROUP BY pva.attribute, pvo.option

, SQL GROUP BY.

+1

,

SELECT products.product_name
, products.product_id
, pvc.combination_id
, pvc.combination
, pva.attribute
, pvo.option
, COUNT(pvo.option) as opt_count
FROM (`products`)
JOIN `product_variant_combinations` pvc ON `products`.`product_id` = `pvc`.`product_id`
JOIN `product_variants` pv ON `pv`.`combination_id` = `pvc`.`combination_id`
JOIN `product_variant_ao_relation` pv_ao ON `pv_ao`.`ao_id` = `pv`.`ao_id`
JOIN `product_variant_options` pvo ON `pvo`.`option_id` = `pv_ao`.`option_id`
JOIN `product_variant_attributes` pva ON `pva`.`attribute_id` = `pv_ao`.`attribute_id`
GROUP BY 1

:

| PRODUCT_NAME | PRODUCT_ID | COMBINATION_ID |                                        COMBINATION | ATTRIBUTE | OPTION | OPT_COUNT |
|--------------|------------|----------------|----------------------------------------------------|-----------|--------|-----------|
|         Desk |     111025 |              4 |                  {"color":"Red","material":"Wood"} |     color |    red |         4 |
|         Lamp |     111024 |              1 |                                    {"color":"Red"} |     color |    red |         3 |
|      T shirt |     111026 |              6 | {"color":"Red","size":"Small","material":"Cotton"} |     color |    red |        18 |
+1

GROUP BY (, max, avg sum), , .

, "" - .

MySQL - , ( ) - , SQL Server, PostgreSQL Oracle , . , , , , .

0

MySQL , " " ​​

, . , , .

{ "": "", "": "" } { " " } { "" : "", "" : "", "": "" } { "" : "", "" : "Medium", "": "" } { "" : "", "" : "", "": "" }

, opt_count 1.

To get around this, you need to get opt_count based only on color = red as a derived table, and then attach this back to the tables that have the rest of the data you are interested in.

I believe this query returns what you want. Note that you are not getting one row for each product, again because of the unique nature of the combination column.

-- Outer query to return the product/option/attribute information, plus the count from the derived table
SELECT
 products.product_name, products.product_id,
 product_variant_combinations.combination_id, product_variant_combinations.combination, 
 product_variant_attributes.attribute, 
 product_variant_options.option,
 red_products.option_count

FROM product_variant_combinations

INNER JOIN product_variants
  ON product_variant_combinations.combination_id = product_variants.combination_id

INNER JOIN product_variant_ao_relation
  ON product_variants.ao_id = product_variant_ao_relation.ao_id

INNER JOIN product_variant_options
  ON product_variant_ao_relation.option_id = product_variant_options.option_id

INNER JOIN product_variant_attributes
  ON product_variant_ao_relation.attribute_id = product_variant_attributes.attribute_id

INNER JOIN products
  ON product_variant_combinations.product_id = products.product_id

INNER JOIN
(
    -- Inner table to count the distinct products with the color "red"
    SELECT COUNT(DISTINCT product_variant_combinations.product_id) AS option_count,
      product_variant_attributes.attribute_id,
      product_variant_options.option_id
    FROM product_variant_attributes

    INNER JOIN product_variant_ao_relation
      ON product_variant_attributes.attribute_id = product_variant_ao_relation.attribute_id

    INNER JOIN product_variant_options
      ON product_variant_ao_relation.option_id = product_variant_options.option_id

    INNER JOIN product_variants
      ON product_variant_ao_relation.ao_id

    INNER JOIN product_variant_combinations
        ON product_variants.COMBINATION_ID = product_variant_combinations.COMBINATION_ID

    WHERE product_variant_options.option = 'red'
    AND product_variant_attributes.attribute = 'color'

    GROUP BY product_variant_attributes.attribute_id, product_variant_options.option_id
) AS red_products
    ON product_variant_attributes.attribute_id = red_products.attribute_id
    AND product_variant_options.option_id = red_products.option_id
0
source

More articles:


All Articles