MYSQL inner join 2 tables and sum()

Question

I am unable to get sum() after joining 2 tables, one is HEADER AND DETAIL. It just gives only 0 result for the below sql statement. Need some help:

My tables:

INVHDR:

    Invno,  Invdate,   Ac_code
    100     2013-04-01  2
    101     2013-04-30  2

    INVDTLS:
    Invno,  Prod_desc, Amount
    100     Argon      155
    100     Argon      250
    101     Oxygen     322
    101     Oxygen     065

Desired result: Sum of amts: 405

MYSQL statement to inner join and sum()

SELECT
        a.Invno, a.Ac_code, a.Invdate, b.*
    FROM INVHDR a
    INNER JOIN (
        SELECT
                Invno, Prod_desc, SUM( Amount ) AS amts
            FROM INVDTLS
            WHERE Prod_desc='Argon'
        ) AS b ON a.Invno = b.Invno
    WHERE
        a.Ac_code='2'
        AND a.Invdate BETWEEN '2013-04-01'
        AND '2013-04-30'
    GROUP BY a.Ac_code

Solution

Why are you writing such a complicated Query, try this:

SELECT sum(b.Amount)
FROM INVHDR a 
INNER JOIN INVDTLS b
ON a.Invno = b.Invno 
WHERE a.Ac_code='2' 
AND a.Invdate BETWEEN '2013-04-01' AND '2013-04-30' 
AND b.Prod_desc='Argon'
--Group by b.Prod_desc,a.Invno, a.Ac_code

Here is the SQL Fiddle

Comments

[sridhar s]

There is an error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM INVHDR a INNER JOIN INVDTLS b ON a.Invno = b.Invno WHERE a.Ac_code='2' AND ' at line 1

[sridhar s]

There is a sum(amount) result but it is not the correct number, instead of 405, it gives 700

[Prahalad Gaggar]

@RaphaëlAlthaus Please have a look at Fiddle. :)

[Anda Iancu]

set the group as needed i.e. customize the query as needed

[Raphaël Althaus]

@AndaIancu no. Please don't give a wrong answer and say "customize it to make it work".

[sridhar s]

When I have more than one invoice number for the same product Argon like 102, Argon, 200. The result it gives is 2 results, 405 and 200. How to solve this, I want total of 605