Grouping data in same column

I have two tables items and values they are related to each other via a pivot table item_value(many-to-many). I can easy get the number of items associated with each value by grouping the query.

SELECT `values`.id as value_id, count(items.id) as item_count
FROM items
INNER JOIN item_value ON item_value.item_id = items.id
INNER JOIN `values` ON item_value.value_id = `values`.id
GROUP BY value_id

But now I want to group some of combinations of values togehter. So the output becomes like this:

| value_id_1 | value_id_2 | item_count |
---------------------------------------
|  1  |  2  |  22  |
|  1  |  3  |  13  |
|  1  |  4  |  0   |

Where do I begin?

sherwinmdev

9 years ago

Level 6

@phx93 group by the 2 columns

SELECT `values`.id as value_id, `values`.id2 as value_id2, count(items.id) as item_count
FROM items
INNER JOIN item_value ON item_value.item_id = items.id
INNER JOIN `values` ON item_value.value_id = `values`.id
GROUP BY value_id, value_id2

phx93

9 years ago

Level 1

@w1n78 but values table only have one ID column. I want to get the count for the combinations of values.id. I want to create a temporary column (alias) as id2 to represent the different combinations of id.

UPDATE

Something like this but the rows doesn't get combined. Would like to get item_count for every combination of attribute_1 and attribute_2.

SELECT v1.id as attribute_1, v2.id as attribute_2, count(items.id) as item_count
FROM items
  INNER JOIN item_value ON item_value.item_id = items.id
  LEFT JOIN `values` v1 ON item_value.value_id = v1.id AND v1.attribute_id = 1
  LEFT JOIN `values` v2 ON item_value.value_id = v2.id AND v2.attribute_id = 2
GROUP BY attribute_1, attribute_2

sherwinmdev

9 years ago

Level 6

@phx93 i tried with some data of mine and this worked. you want it to add the columns each row, right? i used the + between the columns.

SELECT v1.id as attribute_1, v2.id as attribute_2, (v1.id + v2.id), (count(v1.id) + count(v2.id))
FROM items
  INNER JOIN item_value ON item_value.item_id = items.id
  LEFT JOIN `values` v1 ON item_value.value_id = v1.id AND v1.attribute_id = 1
  LEFT JOIN `values` v2 ON item_value.value_id = v2.id AND v2.attribute_id = 2
GROUP BY attribute_1, attribute_2

i wasn't sure what you needed so i did both like below to either add the value of 2 columns or add the value of the count of 2 columns.

(v1.id + v2.id), (count(v1.id) + count(v2.id))

hope that works for you

phx93

9 years ago

Level 1

@w1n78 Hmm I think you misunderstood me. I get the same output as before. I don't get the count for every combination of attribute_1 and attribute_2.

I can solve it by using a subquery for each attribute in the select statement but it is not ideal since I need to do several joins repetitive joins in each subquery. (The real query I'm trying to perform is much more complex and therefore it matters)

sherwinmdev

9 years ago

Level 6

@phx93 oh sorry about that. the code sample in your original post didn't make sense in terms of the value of item_count. so ya i don't understand haha.

so you have a query then? if so, it might be a good idea to move it into a stored procedure so you can off load the work onto the database server rather than the webserver.

Please or to participate in this conversation.