MySQL sum of column group by question

group byMySQL

I have the results of a select that I am joining on that I need to get a slightly complex sum of
a column from.

I need the sum of result grouped on id PLUS the sum of result of any row where parent_id = id.

The data from the join:

cat_id   parent_id   name    result
------   ---------   -----   ------
1        2           name1   7 
1        2           name1   1
2        4           name2   2

For example from the above I need a result like:

cat_id   name    sum(result)
------   -----   -----------
1        name1   8
2        name2   10

Any ideas much appreciated

Best Answer

SELECT id, name, SUM(result)
FROM (SELECT cat_id as id, name, result  FROM test
UNION ALL
SELECT t1.parent_id as id, t2.name, t1.result 
FROM test as t1 
LEFT JOIN test as t2 ON t2.cat_id = t1.parent_id
) as a 
GROUP BY id,name ORDER BY id

Related Solutions

Mysql – Joining two Mysql VIEWs takes a very long time

Using FULLTEXT indexes has to be handled with great care. Why ? While FULLTEXT index searches do work, the MySQL Query optimizer tends to suggest full table scans if you do not express the query properly.

Let's take your query and look for 'tom'

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink
FROM cast_movie as c JOIN film_info as f ON c.ImdbId = f.ImdbId
WHERE c.castName LIKE '%tom%';

This joins everything. Then, it scans the resulting temp table and returns every row with tom.

Let's implement the FULLTEXT method

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink
FROM cast_movie as c JOIN film_info as f ON c.ImdbId = f.ImdbId
WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE);

I can almost assure you that a full table scan will occur during the join because I have experienced it hundreds of times:

Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
Jan 26, 2012 : Mysql fulltext search my.cnf optimization
May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
Jul 18, 2012 : Why full-text-search returns less rows than LIKE

I started writing posts about FULLTEXT and query refactoring ever since I saw a post from webmasterworld.com entitled MySQL Match Against + a very expensive join. How do I optimize this?

Given the information I just mentioned let's take your query and refactor it to handle the FULLTEXT and stilldo a good join.

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink
FROM cast_movie as c JOIN film_info as f ON c.ImdbId = f.ImdbId
WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE);

First, apply the MATCH ... AGAINST operator to cast_movie and return ImdbId and other fields

SELECT ImdbId,movieName,castName,movieImdbId FROM cast_movie
WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE)

Make that a subquery to provide keys to join and retrieve data from film_info

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink FROM
(
    SELECT ImdbId,movieName,castName,movieImdbId FROM cast_movie
    WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE)
) as c JOIN film_info as f ON c.ImdbId = f.ImdbId;

This should produce a better result.

If your tables use InnoDB Storage Engine, remember to tune the InnoDB Fulltext options. Any fulltext option that does not start with innodb_ is meant for MyISAM (See my post MySQL FullText search on string shorter than 3 chars returns no rows)

Give it a Try !!!

Sql-server – How to select distinct TOP but group by column

SELECT InvNr, DetailLine 
FROM tbl_Invoice 
where InvNr in (select distinct  top (2) InvNr from tbl_Invoice order by InvNr);

Best Answer

Related Solutions

Mysql – Joining two Mysql VIEWs takes a very long time

Sql-server – How to select distinct TOP but group by column

Related Question