There are 2 tables - shop_users and shop_users , it is necessary to extract all users with a non-empty shop_users and sort them by the number of items in it. The result must necessarily give shop_users.*

 shop_users ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `password` varchar(55) NOT NULL, ... ) shop_cart ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `item_id` int(11) NOT NULL, `count` int(11) NOT NULL, ... )

    3 answers 3

     select u.*, count(1) cnt from shop_users u join shop_cart c on c.user_id=u.id group by u.id order by cnt

    In your question were discrepancies. It seemed to me that the number of items in the basket is the number of entries in the shop_cart table, the query is written based on this. If the quantity of goods is understood as the sum of the count field from this table, then in the query you must replace count(1) with sum(c.count) .

    If the option only_full_group_by is enabled in MySQL, then in the "group by" you will have to list all the fields from shop_users.

    In general, you would go to some kind of site like sql-ex.ru and teach SQL, it will come in handy. The request is here at the level of first lessons ...

    • join is usually much more expensive than where - rjhdby
    • @rjhdby from where such data? in all sources "just for SQL" they write the opposite. The MySQL documentation says in black and white that the comma operator is fully syntactically equivalent to join and inner join. And it really gives the same execution plan - Mike
    • @rjhdby Just checked on a large table the execution plan for join and where. They are absolutely the same. please support your words with real examples - Mike
    • it means memory fails :) In general, the prejudice against join came when I had to look for bottlenecks in one project, where replacing a two-page query with joines with a much shorter (but identical!) query with simple conditions gave a performance gain of a couple of orders of magnitude. - rjhdby
    • @rjhdby Considering that I generally always worked with Oracle in which, until the 9th version, the word join did not exist, I never use join. But on SO, I try to write according to SQL standards, so that the answer could work on arbitrary RDBMS (well, except for old Oracle :)). - Mike

    You can do the following:

     SELECT u.*, SUM(c.count) AS total FROM shop_cart AS c LEFT JOIN shop_users AS u ON c.user_id = u.id GROUP BY c.user_id ORDER BY total DESC 
       SELECT sum(t2.count) AS cnt, t1.* FROM shop_users t1, shop_cart t2 WHERE t1.id=t2.user_id GROUP BY t1.id HAVING sum(t2.count)>0 ORDER BY cnt DESC
      • I quote the author of the question " The result must necessarily give shop_users. * ". this, in my opinion, means that the result should be all the data from the user table. Are you a different opinion? - Mike
      • @Mike it seems to me that you misinterpreted the question. It clearly states that "all users with a non-empty shopping cart." and shop_users. * is all the FIELDS of this user. By the way, I want to note that your version does not take into account the number of goods ordered (count field) and does not take into account that the count can be equal to 0 - rjhdby
      • There count cannot be equal to zero. because it is used ordinary join, but not left join. And a regular join will not issue a record if at least one of the joined tables does not have a suitable record - Mike
      • @Mike I'm talking about the count field in the shop_cart table. The product can be one, but in 10 copies, then your request will return 1, and mine 10. Also this field, hypothetically, can be equal to 0. But I agree - this is rather the ambiguity of the question that the author considers the quantity of goods in the basket - rjhdby