2

There are my database

articles
id | art_code | art_name
1  | 5678     | computer
2  | 0987     | display
3  | 8547     | keyboard

action
id | code        | action      | qte
1  | 5678        | IN          | 4
2  | 0987        | OUT         | 3
3  | 5678        | IN          | 6
4  | 5678        | OUT         | 2

I would like to SELECT all value of article with SUM() of IN / OUT group by article

ex:

RESULT:

    id | art_code | art_name  | IN  |  OUT 
    1  | 5678     | computer  | 10  |   2  
    2  | 0987     | display   | 3   |   NULL  
    3  | 8547     | keyboard  | NULL|   NULL  

I try but, I've a result with :

RESULT:

    id | art_code | art_name  | IN  |  OUT 
    1  | 5678     | computer  | 10  |   NULL  
    1  | 5678     | computer  | NULL|   2  
    2  | 0987     | display   | 3   |   NULL  

I can't have SUM(IN) and SUM(OUT) in 1 line by article.art_code.

An idea?

  • 1
    Maybe you could give us an SQL fiddle with CREATE TABLE and INSERT INTO statements to give us something to work with? – Vérace Mar 14 '19 at 23:06
1
SELECT id, art_code, art_name, SUM(`IN`) `IN`, SUM(`OUT`) `OUT`
FROM 
  ( SELECT a0.id, a0.art_code, a0.art_name, SUM(a1.qte) `IN`, NULL `OUT`
    FROM article a0
    LEFT JOIN action a1 ON a0.art_code = a1.code AND a1.action = 'IN'
    GROUP BY a0.id, a0.art_code, a0.art_name
  UNION ALL
    SELECT a0.id, a0.art_code, a0.art_name, NULL, SUM(a2.qte)
    FROM article a0
    LEFT JOIN action a2 ON a0.art_code = a2.code AND a2.action = 'OUT'
    GROUP BY a0.id, a0.art_code, a0.art_name
  ) sq
GROUP BY id, art_code, art_name
ORDER BY id;

fiddle

| improve this answer | |
  • Akina, what is it the sq before GROUP BY? – Stolker Mar 16 '19 at 8:21
  • @Stolker It is subquery alias (temporary subquery name). Can be almost any (restrictions - must comply with MySQL object naming rules and be unique within the query). – Akina Mar 16 '19 at 19:04
3

What you are trying to do is called pivoting. You essentially want the results grouped by code and action but re-arranged so that instead of one row per code and action you get one row per code only, all the distinct action values (well, just two in your case) forming separate columns.

In other words, instead of

action   code  value
-------  ----  ------
action1  IN    value1
action1  OUT   value2
action2  IN    value3
...      ...   ...

you are trying to get

action   IN      OUT
-------  ------  ------
action1  value1  value2
action2  value3  ...
...      ...     ...

Some SQL products support a dedicated PIVOT syntax. MySQL is not one of them, but you can use other ways.

One common way is conditional aggregation. You will group the rows by code only, because you want one row per code in the output. The aggregation by action will be achieved by introducing a conditional as the aggregate function's argument. This is how you code that in SQL:

SELECT
  art.id,
  art.art_code,
  art.art_name,
  SUM(CASE act.action WHEN 'IN'  THEN act.qte END) AS `IN`,
  SUM(CASE act.action WHEN 'OUT' THEN act.qte END) AS `OUT`
FROM
  article AS art
  LEFT JOIN action AS act ON art.art_code = act.code
GROUP BY
  art.id,
  art.art_code,
  art.art_name
ORDER BY
  art.id ASC
;

Each SUM expression aggregates qte just for one of the action values, either 'IN' or 'OUT', using a CASE expression. As shown in the query, you need two SUM expressions, one per each value of action. If you had more, you would need to add more SUMs accordingly.

However, as there only two values here, it may make sense to try alternative methods. One such alternative is in Akina's answer. Here is another option:

SELECT
  art.id,
  art.art_code,
  art.art_name,
  act_in.`IN`,
  act_out.`OUT`
FROM
  article AS art

  LEFT JOIN
  (
    SELECT code, SUM(qte) AS `IN`  FROM action WHERE action = 'IN'  GROUP BY code
  ) AS act_in
  ON art.art_code = act_in.code

  LEFT JOIN
  (
    SELECT code, SUM(qte) AS `OUT` FROM action WHERE action = 'OUT' GROUP BY code
  ) AS act_out
  ON art.art_code = act_out.code

ORDER BY
  art.id ASC
;

As you can see, getting each of the two columns (IN and OUT) requires one outer join to a derived table containing the aggregated results for the corresponding action value. It is easy to see how this approach would not be a good idea if you needed to produce ten such columns. Not only would the query itself become cumbersome much more quickly than the conditional aggregation method, but the performance would deteriorate very soon too. However, for just two columns this option might be worth a shot.

Both suggestions presented in this answer can be found and played with at dbfiddle.uk (the setup was borrowed from Akina's fiddle):

| improve this answer | |
0
select distinct a.art_code, a.art_name,(select sum(qte) from test_action where art_code = a.art_code and action = 'IN') as [IN], (select sum(qte) from test_action where art_code = a.art_code and action = 'OUT') as [OUT] from test_articles a left join test_action ac on ac.art_code = a.art_code

Try this query, I think you might have the incorrect expected result. Coded in Microsoft SQL Server

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.