quote:

---

Originally posted by new_bees

I think the problem is adding/sum duplicate value. I tried using Sum(distinct a.QTYSHPPD) and still no luck. But I think I'm getting closer after reading this article. http://www.sqlteam.com/article/how-to-use-group-by-with-distinct-aggregates-and-derived-tables

Are there expert here who can pinpoint where and what needed to be corrected? I'm trying to join 3 tables.

---

quote:

---

Originally posted by new_bees

Thanks guys,

I am confused about this whole project my boss wanted me to do. There are 3 tables that I need to query, all has primary key "ITEMNMBR" where I can joined them through the ITEMNMBR. Now, here is the difficult part I could not figure it out. My boss wants me to use "ITEMDESC" to joined all 3 tables, however only 2 of the 3 tables has the "ITEMDESC" column. I had it working but the problem is is not adding/sum the correct way because there is no ITEMDESC in the other tables. This is weird.

Here is my SQL query. This one is working.
Also, if someone can spot unnecessary code let me know.

SELECT o.ITEMNMBR, o.ITEMDESC AS Description, d.Received AS Received, e.Sold AS Sold
FROM IV00101 o INNER JOIN
(SELECT ITEMNMBR, SUM(QTYSHPPD) AS Received
FROM POP10500
WHERE (DATEPART([year], DATERECD) = '2008') AND (DATEPART([month], DATERECD) = '4') AND QTYSHPPD <> 0
GROUP BY ITEMNMBR) d ON o.ITEMNMBR = d.ITEMNMBR INNER JOIN
(SELECT ITEMNMBR, SUM(QTYORDER) AS Sold
FROM SOP30300
WHERE (DATEPART([year], ACTLSHIP) = '2008') AND (DATEPART([month], ACTLSHIP) = '4')
GROUP BY ITEMNMBR) e ON o.ITEMNMBR = e.ITEMNMBR
ORDER BY o.ITEMDESC

Here is the result.
ITEMNMBR | ITEMDESC | Received | Sold
-------------------------------------------------------------------------------------------------------
ANIRAC110-025KGCTN | Aniracetam | 3.00000 | 3.00000
ARTLEA110-025KGDRM | Artichoke (Synara Scolymus) Leaf P.E. 4:1 | 125.00000 | 125.00000
ASCACI210-025KGCTN | Ascorbic Acid DC97 Cellulose | 10000.00000 | 2075.00000
ASCPAL110-025KGDRM | Ascorbyl Palmitate FCC/USP | 1000.00000 | 185.00000
ASHWAG150-025KGDRM | Ashwagandha Root P.E. 2.5% | 25.00000 | 25.00000
BEEPOL110-020KGCTN | Bee Pollen Powder | 60.00000 | 60.00000
BEEPOL110-025KGDRM | Bee Pollen Powder | 200.00000 | 100.00000
BEEPOL110-025KGDRM | Bee Pollen Powder | 200.00000 | 100.00000

The red color should SUMMED up because they all have same Item description, but the problem is the have different ITEMNMBR.

Any help?

Newbees

---

SELECT     MAX(o.ITEMNMBR) AS ITEMNMBR, o.ITEMDESC AS Description, SUM(d.Received) AS Received, MAX(e.Sold) AS Sold
FROM         IV00101 o INNER JOIN
                          (SELECT     ITEMNMBR, SUM(QTYSHPPD) AS Received
                            FROM          POP10500
                            WHERE      (DATEPART([year], DATERECD) = '2008') AND (DATEPART([month], DATERECD) = '4') AND QTYSHPPD <> 0
                            GROUP BY ITEMNMBR) d ON o.ITEMNMBR = d.ITEMNMBR INNER JOIN
                          (SELECT     ITEMNMBR, SUM(QTYORDER) AS Sold
                            FROM          SOP30300
                            WHERE      (DATEPART([year], ACTLSHIP) = '2008') AND (DATEPART([month], ACTLSHIP) = '4')
                            GROUP BY ITEMNMBR) e ON o.ITEMNMBR = e.ITEMNMBR
GROUP BY o.ITEMDESC
ORDER BY o.ITEMDESC

quote:

---

Originally posted by new_bees

Thank you Visakh, your revised code did the job perfectly. The data from the query match from my manager spreadsheet. I'm glad to be a member of this site, so many good expert people willing to help beginner like myself.

Thank you!

---

SELECT MAX(a.ITEMNMBR) AS ITEMNMBR, a.ITEMDESC AS Description, SUM(b.Received) AS Received, MAX(c.Sold) AS Sold
FROM IV00101 a INNER JOIN
(SELECT ITEMNMBR, SUM(QTYSHPPD) AS Received
FROM POP10500
WHERE (((DATEPART([year], DATERECD) = '2008') AND (DATEPART([month], DATERECD) = '4')) OR DATERECD IS NULL)
GROUP BY ITEMNMBR) b ON a.ITEMNMBR = b.ITEMNMBR INNER JOIN
(SELECT ITEMNMBR, SUM(QTYORDER) AS Sold
FROM SOP30300
WHERE (((DATEPART([year], ACTLSHIP) = '2008') AND (DATEPART([month], ACTLSHIP) = '4')) OR  ACTLSHIP IS NULL) AND (soptype = '3')
GROUP BY ITEMNMBR) c ON a.ITEMNMBR = c.ITEMNMBR
WHERE (b.Received = 0) AND (c.Sold <> 0)
GROUP BY a.ITEMDESC
ORDER BY a.ITEMDESC

quote:

---

Originally posted by new_bees

Visakh I tried your new code and didn't work. It does not count right.

Thanks

---