CODEWITHSUNDEEP
mysqlsqlgroup-by
demonoid
demonoid

Reputation: 326

MySQL group by multiple columns issue

The query below collects temporary overview data for every user into memory table. Basicaly, user sees, count of items by keyword.

The problem is, it calculates total count of items by keyword_id.

What I need is, to calculate item_count by both keyword_id and item_type (Item.type).

 SELECT
        `Item`.`user_id` AS `user_id` ,
        `ItemKeyword`.`keywordID` AS `keyword_id` ,
        `Keyword`.`title` AS `keyword_title`,
        count(`ItemKeyword`.`ItemID`) AS `ico_count`
    FROM
        (
            (
                `ItemKeyword`
                JOIN `Item` ON(
                    (
                        `Item`.`id` = `ItemKeyword`.`ItemID`
                    )
                )
            )
            JOIN `Keyword` ON(
                (
                    `Keyword`.`id` = `ItemKeyword`.`keywordID`
                )
            )
        )
    GROUP BY
        `Item`.`user_id` ,
        `ItemKeyword`.`keywordID`;

Details

For example, now result looks like below

enter image description here

Basicaly, item_count is total of all item_types. What I need is, to separate the result below 

user_id   keyword_id  keyword_title   item_count
1         9645         surveillance    20

Into something like this:

user_id   keyword_id  keyword_title   item_count item_type
1         9645         surveillance    18           1
1         9645         surveillance    2            2

Where, item_count are calculated by both keyword_id and item_type.

I can't figure out how to include item_type also into this query.

Any suggestions?

Upvotes: 0

Views: 57

Answers (1)

cha
cha

Reputation: 10411

I do not understand your love of brackets (parenthesis). Why so many? in my opinion you lose the readability. It was just a side note.

If you need an extra grouping level you need to modify the query like this:

SELECT
    `Item`.`user_id` AS `user_id` ,
    `ItemKeyword`.`keywordID` AS `keyword_id` ,
    `Keyword`.`title` AS `keyword_title`,
    count(`ItemKeyword`.`ItemID`) AS `ico_count`,
    `Item`.`type` AS `item_type` 
FROM `ItemKeyword` JOIN `Item` ON
     `Item`.`id` = `ItemKeyword`.`ItemID`
  JOIN `Keyword` ON
     `Keyword`.`id` = `ItemKeyword`.`keywordID`
GROUP BY
    `Item`.`user_id` ,
    `Item`.`type` ,
    `ItemKeyword`.`keywordID`,
    `Keyword`.`title`;

Upvotes: 2

Related Questions