MYSQL SELECT with JOIN, COUNT and GROUP BY

Question

I have changed my mysql structure and had in the past this query with SUM CASE column keyindex:

SELECT 
    orders.id,
    orders.tool_id,
    date(orders.date_placement) AS cdate,
    CAST(orders.date_placement AS DATE) AS lala,
    positions.id,
    positions.optionindex,
    positions.keyindex,
    tools.id,
    tools.tool_name,
    tools.tool_number,
    SUM(CASE WHEN keyindex=1 THEN 1 ELSE 0 END) AS value1,
    SUM(CASE WHEN keyindex=2 THEN 1 ELSE 0 END) AS value2,
    SUM(CASE WHEN keyindex=3 THEN 1 ELSE 0 END) AS value3,
    SUM(CASE WHEN keyindex=4 THEN 1 ELSE 0 END) AS value4,
    DATE_FORMAT(orders.date_placement, '%Y-%m')  AS nicecdate
 FROM orders
 LEFT JOIN tools  
    ON  tools.id=orders.tool_id
 LEFT JOIN positions
    ON positions.order_id=orders.id
 WHERE
    tools.id = ? 
 group by DATE_FORMAT(orders.date_placement, '%Y-%m')

The result of this query looks:

ID | value1   | value2  | value3  | nicedate  
1  |    1     |  1      |    4    |  2018-09
2  |    0     |  0      |    1    |  2018-10
3  |    1     |  1      |    1    |  2018-11
4  |    2     |  0      |    0    |  2018-12

That is fine to create some stacked charts in frontend.

But now i added an additional table (repkey) with mapped values (ID1=value1, ID2=value2, ....) and i have added a new column to the first table keyindex2 in addition to keyindex.

ID | keyindex1 | keyindex2 | created 
1  |    1      |  27       | 2019-01
2  |    1      |  27       | 2019-01
3  |    2      |  25       | 2019-02
4  |    1      |  27       | 2019-03
5  |    10     |  27       | 2019-04

As result I need every combination from keyindex and keyindex2 based on the corresponding month:

ID | keyindex1 | keyindex2 | Value           |count |created 
1  |    1      |  27       | value1 value27  |  2   |2019-01
2  |    2      |  25       | value2 value25  |  1   |2019-02
3  |    1      |  27       | value1 value27  |  1   |2019-03
4  |    10     |  27       | value10 value27 |  1   |2019-04

This is what i tried but I have no idea how to count the combination of keyindex and keyindex2 of the corresponding month. The result is also different from that of the old structure so I'm not at all sure if this query does exactly what I want.

 SELECT
            orders.id,
            orders.tool_id,
            date(orders.date_placement) AS cdate,
            CAST(orders.date_placement AS DATE) AS lala,
            positions_list.id AS POSITIONID,
            positions_list.order_id AS POSITIONORDERID,
            positions_list.keyindex,
            positions_list.keyindex2,
            RepK.keynr,
            RepK.content AS repcontent,
            RepK.p_company,
            RepK2.keynr,
            RepK2.content AS repcontent2,
            RepK2.p_company,
            COUNT(positions_list.keyindex) AS count,
            COUNT(positions_list.keyindex2) AS count2,
            DATE_FORMAT(orders.date_placement, '%Y-%m') AS nicecdate
      from orders
      JOIN tools
            ON tools.id=orders.tool_id
      JOIN positions_list 
            ON positions_list.order_id = orders.id
      JOIN repkey as RepK
            ON   RepK.keynr=positions_list.keyindex
            AND  RepK.p_company=orders.comp_id
      JOIN repkey AS RepK2
            ON RepK2.keynr=positions_list.keyindex2
      WHERE
            tools.id =:id
      group by DATE_FORMAT(orders.date_placement, '%Y-%m')

BTW: This query is very slow (more then 10 seconds). The Webserver/Database is on nginx with ssd so the issue must be in my query. Are the JOINS the cause?

is not clear what is your question .. is a question about performance ??? .. or about wrong result .. ??? which version of mysql you are using ??? — ScaisEdge, Aug 14 '19 at 11:57
The question is about the wrong result. 10.1.37-MariaDB-1~stretch — Marco, Aug 14 '19 at 12:00
for this you should update your question add a proper (coherent) data sample, your actual result and the expected result. — ScaisEdge, Aug 14 '19 at 12:02

score 1 · Accepted Answer · answered Aug 14 '19 at 14:13

When your needs changed so much that the output and structure of your query isn't the same anymore, it's better to take some distance from the existing code and start the thinking from scrap.

Your need is count the combination of keyindex and keyindex2 of the corresponding month.

We don't know your DB, but this simple need hint strongly in favor of a solution looking like this :

SELECT
        positions_list.keyindex,
        positions_list.keyindex2,
        DATE_FORMAT(orders.date_placement, '%Y-%m') AS nicedate,
        COUNT(*) as count,
        --...more columns
      from orders
      JOIN tools
            ON tools.id=orders.tool_id
      JOIN positions_list 
            ON positions_list.order_id = orders.id
      JOIN repkey as RepK
            ON   RepK.keynr=positions_list.keyindex
            AND  RepK.p_company=orders.comp_id
      JOIN repkey AS RepK2
            ON RepK2.keynr=positions_list.keyindex2
      WHERE
            tools.id =:id
      group by DATE_FORMAT(orders.date_placement, '%Y-%m'),positions_list.keyindex,positions_list.keyindex2

Put the column you want to count separately in the GROUP and it should do the trick. Now depending on your tables relationship, and what you want to count, the COUNT may need some work (for example, if you want to count distinct orders, you may need to use COUNT(DISTINCT orders.id)), but you get the gist.

Also, please take some time to make sure you use JOIN(=INNER JOIN) and LEFT JOIN adequately between each tables, it'll help you get the result you want.

As for the perf, yes JOIN can impact the time your query take but to help you on that we'll need another topic and the details of your DB (most importantly : indexes! you can already check that on your own). There is also a dedicated SE site for DBA.

MYSQL SELECT with JOIN, COUNT and GROUP BY

1 Answers1