CODEWITHSUNDEEP
mysqlsuminner-join
sridhar s
sridhar s

Reputation: 149

MYSQL inner join 2 tables and sum()

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

Upvotes: 3

Views: 13405

Answers (2)

Anda Iancu
Anda Iancu

Reputation: 530

check http://www.sqlfiddle.com/#!2/86377/8 Thank you @Luv

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

Upvotes: -1

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

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

Upvotes: 2

Related Questions