L
L
Leopandro2021-07-08 19:56:23
SQL
Leopandro, 2021-07-08 19:56:23

How to shorten same SELECT code inside SELECT?

The table consists of the following fields
customer, item, total

select web_deal.customer, (
        SELECT group_concat(DISTINCT item)
        ) as gems, sum(web_deal.total) as spent_money
    from web_deal
    where web_deal.item in (
        select item
        from
            (select web_deal.customer as customer,
                    web_deal.item
             from web_deal
             where customer IN (
                 select web_deal.customer from web_deal
                 group by web_deal.customer
                 order by count(web_deal.total) DESC
                 limit 5
             )
             group by web_deal.customer, web_deal.item
             order by sum(web_deal.total) DESC)
        group by item
        having count(item) > 1
    )
    group by web_deal.customer
    order by count(web_deal.total) DESC
    limit 5

you need to select 5 customers who ordered the most items, displaying items that at least 2 customers from the top 5 have.
The code works fine, but there are 2 times the choice of the top 5 customers, how can I fix this?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question