1

I want to fetch the most ordered items from the database.

For example: I have a following table:

S.no-----Name--------Items

1---------rob------------Pen, pencil, rubber

2---------jim------------rubber, scissor

3--------rick------------pen,pencil

I want to fetch most ordered items as shown below

S.no---item-------Count

1------Pen---------2

2------Pencil------2

3------Rubber------1

4------Scissor-----1

Below is the query i am using

SELECT name, items, count(items) AS cnt FROM tbl_orderitem GROUP BY items ORDER BY cnt DESC

but am not able to achieve the result as i want

Peace
  • 616
  • 2
  • 8
  • 24
  • what is the programing language you are using to access mysql? also can you list the table columns you are using? – Clint Aug 04 '18 at 12:36
  • Do you have a table where you have all the items? If yes, then it's very much possible. – Sookie Singh Aug 04 '18 at 12:38
  • 4
    Your schema appears to be a mess. Show us the table definitions. – nicomp Aug 04 '18 at 12:39
  • 5
    Step 1. See normalisation. – Strawberry Aug 04 '18 at 12:42
  • Given what appears to be your schema - where `Items` is a comma separated list of items - the only way to achieve what you want in MySQL is with a stored procedure which will be slow. You would be better doing it in whatever application language you are using. Also see @Strawberry comment – Nick Aug 04 '18 at 12:44
  • "the only way to achieve what you want in MySQL is with a **stored procedure** which will be slow" No nick.. you can do it without a stored procedure by making a MySQL number generator and nested `SUBSTRING_INDEX` functions.. Something like this https://stackoverflow.com/questions/46890617/restructuring-a-bad-database-with-php-loops-or-mysql/46892143#46892143 (post off mine) .. But normalisation is the best step because i already see `Pen` and `pen` in the data structure so ine trick wil not work on this data structure without a `LOWER()` function.. – Raymond Nijland Aug 04 '18 at 13:00
  • 1
    But normalisation suggestion is the best step because i already see `Pen` and `pen` in the data structure. Problem with normalisation is that the application code also needs to be updated to support it.. Why you should not use comma separated values in a database https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Raymond Nijland Aug 04 '18 at 13:05
  • @RaymondNijland I stand corrected. A neat trick although I'm guessing it is still slow. – Nick Aug 04 '18 at 13:08
  • "I'm guessing it is still slow." Slow is pretty relative here besides you are comparing the performance of two different structures here.. Normalisation vs comma separated values.. Offcource normalisation is the better structure.. But normalisation can also be slow when you select data without correct indexes while joining on large tables.. @Nick – Raymond Nijland Aug 04 '18 at 13:20

1 Answers1

2

Here's a way. You need to split those Items into rows.
Then from that you can group by and count.

The example SQL below uses a sub-query for only 4 numbers.
But that can be replaced by a Tally Table with just numbers.

A Sql Fiddle test can be found here

SELECT
  TRIM(
    LOWER(
      SUBSTRING_INDEX(
       SUBSTRING_INDEX(Items, ',', numbers.n), ',', -1))) AS Item,
  COUNT(*) AS Total
FROM testtable
JOIN (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) numbers 
  ON CHAR_LENGTH(Items)-CHAR_LENGTH(REPLACE(Items,',','')) >= numbers.n-1
GROUP BY Item
ORDER BY Total DESC;
LukStorms
  • 28,916
  • 5
  • 31
  • 45
  • 1
    @Peace Do note that it's a solution that would work in MySql 5.x. But I believe this can be done differently in MySql 8.x or MariaDB. – LukStorms Aug 04 '18 at 14:17
  • 1
    @Peace Just a warning. If there are more than 3 items per user. Don't forget that numbers needs more than just 4. This is just an example after all. Using a permanent table with just numbers instead of the sub-query is probably best. – LukStorms Aug 04 '18 at 20:09