SQL Combining fields in one table

Question

There is a script that displays two tables. Names of type String and counting of type Long. How can I combine the same fields “Out of stock” in one field and separate fields “marriage”, “re-sorting” in one field “marriage / re-sorting” in one table. In doing so, save the types of two tables and get the combined values in the new fields. And how not to display extra fields, for example, a form for employees, etc. I know that you can use the CASE, WHEN, THEN structure. But I don’t understand how to correctly describe it in my script.

SELECT rl.reason AS reject_reason, COUNT(*)
FROM
    mp.reservation_log AS rl
    JOIN
    mp.store AS st ON rl.store_id = st.md_id
    JOIN mp.order_item oi ON oi.reserve_id=rl.reservation_id
    JOIN mp.sku s ON s.id=oi.item_id
    JOIN mp.product p ON p.id=s.product_id
WHERE rl.created_at > DATE(NOW()) - INTERVAL 1 MONTH AND rl.is_successful=0
GROUP BY rl.reason;

Table example:

table example

Sample data and desired results would help your question make sense. Nothing in your query is called "marriage" or "out of stock" or whatever, so it is totally unclear what you are referring to. Further, your first sentence says "two tables" but your query references *five*. — Gordon Linoff, Mar 22 '20 at 13:39
more tables yes, I attached the output that needs to be changed. Pay attention to this. — skydogs, Mar 22 '20 at 13:49
Even after your attached output (don't do that in a picture!), it is unclear what your answer is to the question `Nothing in your query is called "marriage" or "out of stock" or whatever, so it is totally unclear what you are referring to.` — Luuk, Mar 22 '20 at 13:53

score 0 · Answer 1 · answered Mar 22 '20 at 14:16

0

It seems like you want to combine several reasons in the same group.

You can use a case expression as a no-aggregated column for this, like so:

select
    case 
        when r1.reason in ('marriage', 're-sorting') then 'marriage/re-sorting'
        else r1.reason
    end real_reason,
    count(*) cnt
from ...
where rl.created_at > current_date - interval 1 month and rl.is_successful = 0
group by real_reason

Side note: DATE(NOW()) is better written CURRENT_DATE .

answered Mar 22 '20 at 14:16

GMB

216,147
25
84
135

Could you help me combine this with my request? I constantly get a lot of mistakes – skydogs Mar 22 '20 at 14:59
@skydogs: you just have to replace the `...` in my answer with your `from` clause (including the `join`s). – GMB Mar 22 '20 at 15:00

SQL Combining fields in one table

1 Answers1