Database Discussion - Needs Help with Creating SQL Statement

Print Page | Close Window

Needs Help with Creating SQL Statement

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=14882
Printed Date: 29 March 2026 at 7:46pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Needs Help with Creating SQL Statement

Posted By: Misty
Subject: Needs Help with Creating SQL Statement
Date Posted: 29 April 2005 at 1:45pm

Can someone please help me to create a sql statement for the following information:

1.) How many orders were made for an item a certain month?

2.) How many orders were made for an item in a year?

Replies:

Posted By: dj air
Date Posted: 29 April 2005 at 2:17pm

Dim intSearchMonth, intSearchYear, intItemID

intSearchMonth = month to search
intSearchYear = year to search for
intItemID = ID for the item your searching

' SQL for items for a certain month
strSQL = "SELECT Count(ItemID) AS Ordered FROM tblOrders WHERE Month(tblOrders.Order_Date) = " & intSearchMonth & " AND tblOrders.ItemID = " & intItemID & " "

' SQL for items for a certain Year
strSQL = "SELECT Count(ItemID) AS Ordered FROM tblOrders WHERE Year(tblOrders.Order_Date) = " & intSearchYear & " AND tblOrders.ItemID = " & intItemID & " "

the above is on the idea you have a orders table holding the Item ID that was ordered. without more details we can't get it exact. hope the above help

Posted By: ljamal
Date Posted: 29 April 2005 at 4:47pm

The only correction I would make is that �t's probably a specific month AND year, so the month query should include the year as well.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: dj air
Date Posted: 29 April 2005 at 5:18pm

good point cheers for that.. heres a re write below

Dim intSearchMonth, intSearchYear, intItemID

intSearchMonth = month to search
intSearchYear = year to search for
intItemID = ID for the item your searching

' SQL for items for a certain month
strSQL = "SELECT Count(ItemID) AS Ordered FROM tblOrders WHERE Month(tblOrders.Order_Date) = " & intSearchMonth & " AND tblOrders.ItemID = " & intItemID & " AND Year(tblOrders.Order_Date) = " & intSearchYear & " "

' SQL for items for a certain Year
strSQL = "SELECT Count(ItemID) AS Ordered FROM tblOrders WHERE Year(tblOrders.Order_Date) = " & intSearchYear & " AND tblOrders.ItemID = " & intItemID & " "

Posted By: Misty
Date Posted: 01 May 2005 at 2:05am

Thanks for the help! I need some more help. How would I write a sql query if I had the two following tables: Customers and Web Hosting. There is a WebHostingID field in both table. Some customers may not have a web hosting ID. I'd like to show all of the customers that have either a value or no value in the WebHostingID. How do I accomplish this?

Posted By: one-ring
Date Posted: 16 May 2005 at 10:27pm

Hi Misty!

i'm not sure if you still need answer to your last query but just the same, here is my suggestion:

if you want to know all the customers that have web hosting record, try this:

select C.*
from CUSTOMERS as C
inner join WEBHOSTING as WH on C.webhostingid = WH.webhostingid

if you want to know all the customers that have no web hosting record, try this:

select C.*
from CUSTOMERS as C
left outer join WEBHOSTING as WH on C.webhostingid = WH.webhostingid
where WH.webhostingid is null

Hope this helps Smile

-------------
" A man may fail many times,
But he isn't a failure
Until he begins to blame someone else."

--- Knox Manning