sqljoomlaphpmyadminvirtuemart

How do I display grouped ID's as a list with their respective values?

How do I not get all the ID's grouped, but instead listed from first to last; with all their respective values in the columns next to them?

Instead of grouping it, it should show ID 1 and its value, ID 2 and its value. EVEN if the values for the ID is the same. I tried removing the GROUP_CONCAT, but then it's only showing one ID per customfield_value?

SELECT GROUP_CONCAT(virtuemart_product_id), customfield_value, COUNT(*) c
FROM jos_virtuemart_product_customfields
WHERE virtuemart_custom_id = 6
GROUP BY customfield_value
HAVING c > 1

It's working currently, but grouping the ID's and spacing them with a comma. Should just display as in a normal table/list format.

Currently it shows like this(as you can see, it's ALL the same ICOS number, but different ID's. I ONLY need to display the values WHERE the ICOS NUMBER is "duplicate"):

   ID    ICOS  Count
1,2,3  775896      3

It should be displaying like this:

   ID    ICOS  Count
    1  775896      1
    2  775896      1
    3  775896      1
Solution

  • All rows where the customfield_value is not unique:

    -- Assuming MySQL
SELECT virtuemart_product_id, customfield_value
   , COUNT(*) c -- maybe not needed
FROM jos_virtuemart_product_customfields
WHERE virtuemart_custom_id = 6
  AND customfield_value IN
 ( SELECT customfield_value
   FROM jos_virtuemart_product_customfields
   WHERE virtuemart_custom_id = 6
   GROUP BY customfield_value
   HAVING COUNT(*) > 1 -- more than one row exists
 ) 
GROUP BY virtuemart_product_id, customfield_value -- maybe not needed

    If the virtuemart_product_id is unique you don't need the outer count/group by as it will always be 1.