1

SQL Server 2008 - This is my standard export

OrderDetailID - OrderID - ProductName - TotalPrice - ShipDate

34                     16           Green...           5.00        4/9/16
35                     16           Green...           3.00        4/9/16
36                     16           Blue...            8.00        4/9/16
37                     17           Green...           9.00        4/11/16
38                     17           Red...             3.00        4/11/16
39                     18           Blue...            5.00        4/11/16
40                     19           Green...           4.00        4/11/16
41                     19           Red...             6.00        4/11/16
42                     20           Green...           3.00        4/11/16
43                     20           Green...           3.00        4/11/16

I need an output of all OrderIDs that contain a total sum >= 5.00 for green products bought today. (Think of it as a Saint Patricks Day Sale, buy 5.00$ worth of green items, qualify for output.)

End result would be:

OrderID

17

20

I know I can do this in excel, but having me do it every day is not something I want. Luckily, I have access to a built-in API which allows me to set stored SQL queries, so if I can find out how to word this, theoretically anyone should be able to click 1 button and get the results they desire (based on me editing the criteria as needed, ie: green, >5, ect)

So far i'm around something like this

SELECT table.OrderID
WHERE table.ProductName LIKE '%green%'
AND SUM(table.TotalPrice) > 5 
GROUP BY table.OrderID
FROM table

It just keeps coming back

Incorrect syntax near the keyword 'FROM'.

Maybe someone will answer, hopefully someone can point me in the right direction, and if I figure this out I'll make sure to update.

  • 2
    learn sql syntax before writing queries. – Vamsi Prabhala Apr 11 '16 at 16:00
  • I'm an excel junkie by day, but a question was proposed to me about a SQL query. I'm not a DBA or software engineer or anything, so I just needed a little push in the right direction. Had some trouble wrapping my head around this simple process which I know I can logically execute in excel, just didn't know the syntax like you said or how to word it, but I figured i'd ask why my logic is failing before learning SQL completely. Turns out the where function is limited! Appreciate the concern though, and I would not say I can write SQL, so you are completely right. –  Apr 11 '16 at 16:21

1 Answers1

1
SELECT  table.OrderID
FROM    table
WHERE   table.ProductName LIKE '%green%'
GROUP BY
        table.OrderID
HAVING  SUM(table.TotalPrice) >= 5
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • Having! Much appreciated, incredible response time. This place is full of the best :) –  Apr 11 '16 at 16:15