sql - relacionadas - Unión interna con cuenta() en tres tablas

unir dos consultas sql misma tabla

Question

Pregunta simple y rápida, tengo esas tablas:

//table people
| pe_id | pe_name |
| 1  | Foo  |
| 2  | Bar  |
//orders table
| ord_id | pe_id | ord_title   |
|   1    |   1   | First order |
|   2    |   2   | Order two   |
|   3    |   2   | Third order |
//items table
| item_id | ord_id | pe_id | title  |
|   1     |   1    |   1   | Apple  |
|   2     |   1    |   1   | Pear   |
|   3     |   2    |   2   | Apple  |
|   4     |   3    |   2   | Orange |
|   5     |   3    |   2   | Coke   |
|   6     |   3    |   2   | Cake   |

Necesito tener una consulta que incluya a todas las personas, contando el número de pedidos y el número total de artículos, así:

| pe_name | num_orders | num_items |
| Foo  |    1       |   2       |
| Bar  |    2       |   4       |

Pero no puedo hacerlo funcionar! Lo intenté

SELECT
    people.pe_name,
    COUNT(orders.ord_id) AS num_orders,
    COUNT(items.item_id) AS num_items
FROM
    people
    INNER JOIN orders ON (orders.pe_id = people.pe_id)
    INNER JOIN items ON items.pe_id = people.pe_id
GROUP BY
    people.pe_id;

Pero esto devuelve los valores num_* incorrectos:

| name | num_orders | num_items |
| Foo  |    2       |   2       |
| Bar  |    8       |   8       |

Noté que si trato de unirme a una mesa a la vez, funciona:

SELECT
    people.pe_name,
    COUNT(orders.ord_id) AS num_orders
FROM
    people
    INNER JOIN orders ON (orders.pe_id = people.pe_id)
GROUP BY
    people.pe_id;

//give me:
| pe_name | num_orders |
| Foo     |          1 |
| Bar     |          2 |

//and:
SELECT
    people.pe_name,
    COUNT(items.item_id) AS num_items
FROM
    people
    INNER JOIN items ON (items.pe_id = people.pe_id)
GROUP BY
    people.pe_id;
//output:
| pe_name | num_items |
| Foo     |         2 |
| Bar     |         4 |

¿Cómo combinar esas dos consultas en una?

Answer 1

¡Tiene más sentido unir el artículo con las órdenes que con la gente!

SELECT
    people.pe_name,
    COUNT(distinct orders.ord_id) AS num_orders,
    COUNT(items.item_id) AS num_items
FROM
    people
    INNER JOIN orders ON orders.pe_id = people.pe_id
         INNER JOIN items ON items.ord_id = orders.ord_id
GROUP BY
    people.pe_id;

Unir los elementos con la gente provoca muchos doblones. Por ejemplo, los elementos de la torta en el orden 3 se vincularán con el orden 2 a través de la unión entre las personas, ¡y no querrás que esto suceda!

Asi que :

1- Necesitas una buena comprensión de tu esquema. Los artículos están vinculados a pedidos, y no a personas.

2- Necesita contar órdenes distintas para una persona, de lo contrario contará tantos artículos como órdenes.

Answer 2

Como Frank señaló, necesitas usar DISTINCT. Además, dado que está utilizando claves primarias compuestas (lo que es perfectamente correcto, por cierto), debe asegurarse de que utiliza la clave completa en sus combinaciones:

SELECT
    P.pe_name,
    COUNT(DISTINCT O.ord_id) AS num_orders,
    COUNT(I.item_id) AS num_items
FROM
    People P
INNER JOIN Orders O ON
    O.pe_id = P.pe_id
INNER JOIN Items I ON
    I.ord_id = O.ord_id AND
    I.pe_id = O.pe_id
GROUP BY
    P.pe_name

Sin I.ord_id = O.ord_id se unía cada fila de artículo a cada fila de orden para una persona.

Answer 3

Intenté poner distinto en ambos, count (distinto ord.ord_id) como num_order, count (distinto items.item_id) como num items

esta funcionando :)

    SELECT
         people.pe_name,
         COUNT(distinct orders.ord_id) AS num_orders,
         COUNT(distinct items.item_id) AS num_items
    FROM
         people
         INNER JOIN orders ON (orders.pe_id = people.pe_id)
         INNER JOIN items ON items.pe_id = people.pe_id
    GROUP BY
         people.pe_id;

Gracias por el hilo que ayuda :)

Answer 4

Su solución es casi correcta. Usted podría añadir DISTINCT:

SELECT
    people.pe_name,
    COUNT(distinct orders.ord_id) AS num_orders,
    COUNT(items.item_id) AS num_items
FROM
    people
    INNER JOIN orders ON (orders.pe_id = people.pe_id)
    INNER JOIN items ON items.pe_id = people.pe_id
GROUP BY
    people.pe_id;

Answer 5

Uno tiene que entender lo que hace un JOIN o una serie de JOIN a un conjunto de datos. Con la publicación de strae, un pe_id de 1 unido con el orden correspondiente y los elementos en pe_id = 1 le dará los siguientes datos para "seleccionar" de:

[porción de personas de la mesa] [porción de órdenes de la mesa] [porción de los elementos de la tabla]

| people.pe_id | people.pe_name | orders.ord_id | orders.pe_id | orders.ord_title | item.item_id | item.ord_id | item.pe_id | item.title |

| 1 | Foo | 1 | 1 | Primer orden | 1 | 1 | 1 | Manzana |
| 1 | Foo | 1 | 1 | Primer orden | 2 | 1 | 1 | Pera

Las uniones esencialmente vienen con un producto cartesiano de todas las tablas. Básicamente tienes ese conjunto de datos para seleccionar y es por eso que necesitas un conteo distinto en orders.ord_id y items.item_id. De lo contrario, ambos conteos darán como resultado 2, ya que efectivamente tiene 2 filas para seleccionar.

Answer 6

select pe_name,count( distinct b.ord_id),count(c.item_id) 
 from people  a, order1 as b ,item as c
 where a.pe_id=b.pe_id and
b.ord_id=c.order_id   group by a.pe_id,pe_name