sql query- Most common array element from a column with different arrays

Asked Mar 25 '18 at 22:03

Active Mar 26 '18 at 08:03

Viewed 137 times

hello i am stuck at a problem, i have database where i have a column like this whose name is "tileused". it has tile names separated by "," comma. I need to display list of all tile names with their count in desc order.
for eg:
column name: tileused

And single cell looks like this:
"Antarca Rose Decor 2_90_30_Matt_AGL Official_patterned_1395.jpg,Antarca White_90_30_Matt_AGL Official.jpg,Antarca White_90_30_Matt_AGL Official.jpg,Antarca Rose Decor 3_90_30_Matt_AGL Official_patterned_1395.jpg,Antarca White_90_30_Matt_AGL Official.jpg,Antarca White_90_30_Matt_AGL Official.jpg,Satin Bianco_30_30_Matt_AGL.jpg"

all tile names separated by "," comma.

I need result as

<html>
<body>
<table width="" border="1" cellpadding="1" cellspacing="1">
<tr>
<th> tile name </th>
<th> count </th>
</tr>
<tr><td>Antarca Rose Decor 2_90_30_Matt_AGL Official_patterned_1395.jpg</td>
<td>1300</td></tr>
</body>
</table>

so far i have seen this solution, but haven't worked https://stackoverflow.com/a/37450240/9510852

If you can help me, it would be great. thanks in advance

full view of tablehere

edited Mar 26 '18 at 08:03

asked Mar 25 '18 at 22:03

Shubham Maheshwari

1

When you say "single cell" what are you referring to? – Nick Mar 25 '18 at 22:09
1

Storing a comma-separated list of values in a column is very bad database design. With such a poor design, working with the data is very difficult. – Honeyboy Wilson Mar 25 '18 at 22:22
@Nick that is database cell. the column "tileused" has 1 of the entry as: single cell. – Shubham Maheshwari Mar 26 '18 at 07:35
@HoneyboyWilson What could be the better design for this. – Shubham Maheshwari Mar 26 '18 at 07:36
Can you post the entire table structure? It's difficult to solve the problem without it. – Nick Mar 26 '18 at 07:39
@Nick added , kindly check – Shubham Maheshwari Mar 26 '18 at 08:04
I don't see any columns related to tiles in that image? – Nick Mar 26 '18 at 08:13
12th column "tileused" – Shubham Maheshwari Mar 26 '18 at 08:32
@Nick https://i.stack.imgur.com/mj0Qu.png – Shubham Maheshwari Mar 26 '18 at 08:33
Ok, so where is the column which contains the comma separated list of tiles? – Nick Mar 26 '18 at 08:52
@Nick that's the column "tileused" – Shubham Maheshwari Mar 26 '18 at 10:38
Ok, sorry I misunderstood. Is there a column which just has the individual tile names in it? – Nick Mar 26 '18 at 12:12
yes, but why would that help ? – Shubham Maheshwari Mar 26 '18 at 17:40

sql query- Most common array element from a column with different arrays

0 Answers0