May 5, 2015 at 1:54 pm
I am trying to create a script file that will get me the total number of orders in july. How exactly would i say july because i know my syntax is wrong and I would be using sum instead of count right?
Please help this is what i tried
use Cis11101_Northwind
Declare @Julycount int
Set @Julycount= (Select sum(*) From orders Where OrderDate = 'july')
print 'The total orders for july is ' + Cast(@JulyCount as varchar)
May 5, 2015 at 2:02 pm
minimay (5/5/2015)
I am trying to create a script file that will get me the total number of orders in july. How exactly would i say july because i know my syntax is wrong and I would be using sum instead of count right?Please help this is what i tried
use Cis11101_Northwind
Declare @Julycount int
Set @Julycount= (Select sum(*) From orders Where OrderDate = 'july')
print 'The total orders for july is ' + Cast(@JulyCount as varchar)
I would assume you also want a year? Also, you stated in your description that you want the number of orders. Wouldn't that be a COUNT? You can't sum all columns, that just simply makes no sense.
Given the table name looks a LOT like homework I am not going to give you an answer. You should look up the datetime datatype in BOL (Books online), aka msdn, aka technet. You should also look aggregation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 5, 2015 at 2:05 pm
nevermind i figured it out
Declare @Julycount int
Set @Julycount= (Select Count(*) From orders Where MONTH(OrderDate) = 7)
print 'The total orders for july is ' + Cast(@JulyCount as varchar)
May 5, 2015 at 2:16 pm
minimay (5/5/2015)
nevermind i figured it out
Declare @Julycount int
Set @Julycount= (Select Count(*) From orders Where MONTH(OrderDate) = 7)
print 'The total orders for july is ' + Cast(@JulyCount as varchar)
That will get you the count of orders in July for ALL years. Once you have orders in more than 1 year I don't think this will do what you want.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 5, 2015 at 2:21 pm
minimay (5/5/2015)
nevermind i figured it out
Declare @Julycount int
Set @Julycount= (Select Count(*) From orders Where MONTH(OrderDate) = 7)
print 'The total orders for july is ' + Cast(@JulyCount as varchar)
You also have a nonSARGable predicate in your where clause. This means that your query will not be able to use any indexing on the OrderDate column to help the performance of this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 5, 2015 at 2:22 pm
Sean Lange (5/5/2015)
minimay (5/5/2015)
nevermind i figured it out
Declare @Julycount int
Set @Julycount= (Select Count(*) From orders Where MONTH(OrderDate) = 7)
print 'The total orders for july is ' + Cast(@JulyCount as varchar)
That will get you the count of orders in July for ALL years. Once you have orders in more than 1 year I don't think this will do what you want.
You could add logic to the where clause for a specific year, if you wanted.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
May 5, 2015 at 9:41 pm
minimay (5/5/2015)
nevermind i figured it out
Declare @Julycount int
Set @Julycount= (Select Count(*) From orders Where MONTH(OrderDate) = 7)
print 'The total orders for july is ' + Cast(@JulyCount as varchar)
This is absolutely the worst way to do it for all of the reasons the others have stated above and maybe a couple more. 😉
As some have stated, this will include all years of data for July and I strongly suspect that whether this be for a class or is a real business requirement, it will be horribly wrong and you'll either fail your class or get fired.
What are the business requirements that you're actually trying to solve?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 6, 2015 at 9:06 am
Im not trying to solve anything Im trying to figure out how to do this for future reference
May 6, 2015 at 9:23 am
minimay (5/6/2015)
Im not trying to solve anything Im trying to figure out how to do this for future reference
Dealing with dates in sql is a topic that many people don't understand and as a result they create very poor performing queries, or queries that simply don't work. I would recommend changing your query to something more like this. Aside from the difference in evaluating the date also notice that I specified the size of your varchar. If you don't do this it will be the default length. This default changes based on how it is used. In your case it would be 30 but in other cases it will be 1.
Declare @Julycount int
Select @Julycount = Count(*)
From orders
Where OrderDate >= '2015-07-01'
and OrderDate < '2015-0801'
print 'The total orders for july is ' + Cast(@JulyCount as varchar(4))
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply