Select SUM of products where name before the space is the same

• crazy_new

  SSCarpal Tunnel

  Points: 4804

  More actions

  February 18, 2015 at 5:20 am

  #301269

  Hi All,

  I need to get the total sales for every product sold in the last month. That is easy enough if the product only has one quantity.

  I have for example:

  Product ID Sales

  Coca-Cola 340ml 1 2

  Coca-Cola 500ml 2 2

  Coca-Cola 2L 3 6

  Iron Brew 340ml 4 1

  Iron Brew 500ml 5 4

  select LEFT(Product, charindex(' ', Product) - 1),sum(qty) as QTY

  from mpsales.salesreports

  where datepart(month,invoicedate) = 1

  group by Product

  order by Product,qty desc

  This shows:

  Coca-Cola 2

  Coca-Cola 2

  Coca-Cola 6

  Iron Brew 1

  Iron Brew 4

  Instead I want to see:

  Coca-Cola 10

  Iron Brew 5

  Can anyone please point me in the right direction?

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  February 18, 2015 at 5:23 am

  #1778309

  Untested, but may be as simple as changing

  group by Product

  to

  group by LEFT(Product, charindex(' ', Product) - 1)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• crazy_new

  SSCarpal Tunnel

  Points: 4804

  More actions

  February 18, 2015 at 7:29 am

  #1778342

  Thanks Mark, works perfectly