Adding Total Sales

Question

I'm having issues getting a total count of items sold. The script below shows me the total sales but and not summing them up. I got it to sum them up but it was listing duplicate invoices so I enter “distinct” before invoice.no. I can't figure out where I'm messing this up. Mind you I have no previous SQL training, I've only gotten this far from reading other readers’ questions here. Please help. Thanks in advance.

SELECT DISTINCT 
    sh.invoice_no, sr.item_id, sr.item_desc, sr.last_sale, sr.SQ, 
    sr.DQ, sr.AQ, sr.VQ,sr.PQ, sr.iQOH, sr.avg_MAC, si.cost, 
    sr.PT, si.item_id AS Item, SUM(sh.qty_shipped) AS QTY_Shipped
FROM iml_view_item_stock_review_nh as sr 
INNER JOIN p21_view_inventory_supplier as si ON sr.item_id = si.item_id 
INNER JOIN p21_sales_history_view as sh ON sr.item_id = sh.item_id
WHERE (sr.item_id LIKE 'IEI%') 
  AND (sr.PT = 'R') 
  AND (sh.invoice_date > CONVERT(DATETIME, '2013-10-17 00:00:00', 102)) 
GROUP BY 
    sr.item_id, sr.item_desc, sr.last_sale, 
    sr.SQ, sr.DQ, sr.AQ, sr.VQ, 
    sr.PQ, sr.iQOH, sr.avg_MAC, si.cost, 
    sr.PT, si.item_id, sh.invoice_no

Below is a sample of the data. I omitted some of the other columns. Hope this helps.

|invoice_no |   item_id    |  QTY_Shipped |
|-----------------------------------------|
| 1000616   |  IEI0803000F |       4      |
| 1001289   |  IEI0803000F |       2      |
| 1018242   |  IEI0803000F |       2      |
| 1001224   |     IEI212I  |       2      |
| 1002265   |     IEI212I  |       2      |
| 1003547   |     IEI212I  |       2      |

The Expected output is something like:

|   item_id    |  QTY_Shipped |
|--------------|--------------|
| IEI0803000F  |       8      |
|   IEI212I    |       6      |

Sorry if I don't have much knowledge. My company is sending me to get trained and certified in January and soon I'll be helping others. Thanks in advance.

"select distinct" is completely redundant in that query, it does nothing at all and is probably ignored. Please don't use "select distinct" when using "group by" — Paul Maxwell, Oct 17 '14 at 22:11
your code looks like it is for SQL Server, not MySQL. What database is it please? — Paul Maxwell, Oct 17 '14 at 22:12
oh, "distinct" does **NOT** work just on the field immediately after it. "select distinct" works over the **whole row** and that's why it isn't doing what you expected. — Paul Maxwell, Oct 17 '14 at 22:18
It's impossible for us to know how those 3 tables **should** work together. Maybe they need to be joined differently but we don't know anything about the tables or the data in them. To get a better answer you need to supply "sample data" (per table) and an "expected result". Not vast amounts of data, just a small sample; and the expected result is derived from that sample. Perhaps http://sqlfiddle.com can help you prepare the sample — Paul Maxwell, Oct 17 '14 at 22:32
I'm sorry it is SQL not MySql. I'll provide some sample data. Thank you! — Gabriel Padilla, Oct 17 '14 at 23:02
By SQL you mean Microsoft SQL Server? Try to add make your tables and table data like this [question](http://stackoverflow.com/questions/26434262/postgres-left-join-with-sum-missing-records) — Ram, Oct 17 '14 at 23:15
You've added sample data, but we still need the expected output base don that data. And,, fwiw, you'll be well served by referring to tutorials rather than only questions here. For example http://www.tsql.info/select-query/group-by.php. — Karl Kieninger, Oct 18 '14 at 00:27

score 0 · Answer 1 · answered Oct 23 '14 at 16:01

It turn out that my query was right the whole time. I was just shocked by the data.

SELECT    sr.item_id, sr.item_desc, sr.last_sale, sr.SQ, 
          sr.DQ, sr.AQ, sr.VQ,sr.PQ, sr.iQOH, sr.avg_MAC, 
          si.cost, sr.PT, si.item_id AS Item, SUM(sh.qty_shipped) AS QTY_Shipped
FROM      iml_view_item_stock_review_nh as sr INNER JOIN
          p21_view_inventory_supplier as si ON sr.item_id = si.item_id INNER JOIN
          p21_sales_history_view as sh ON sr.item_id = sh.item_id
WHERE     (sr.item_id LIKE 'IEI%') AND (sr.PT = 'R') AND 
          (sh.invoice_date > DATEADD(YEAR,-1,GETDATE())) 


GROUP BY          sr.item_id, sr.item_desc, sr.last_sale, 
                  sr.SQ, sr.DQ, sr.AQ, sr.VQ, 
                  sr.PQ, sr.iQOH, sr.avg_MAC, si.cost, 
                  sr.PT, si.item_id

Adding Total Sales

1 Answers1