sql syntax

Hi, I'm using this command to group data by year and month, it works but I was wondering if there was a better way to do this than calling 3 times the function year.

select succ, year(datefact) as year, month(datefact) as month, sum(mnt_facture) as total from hccfact group by succ, year(datefact) , month(datefact) having year(datefact)  = '2015'

Is there a way to call it more something like this since I'm already giving it a name in the select part :
select succ, year(datefact) as year, month(datefact) as month, sum(mnt_facture) as total from hccfact group by succ, year , month having year  = '2015'

I don't know if this would have an impact on optimization or if I'm already using the best way.
Buropro-CitationAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
The only optimization that i can think which you can do is using a where clause instead of having.. which is this..

select succ, year(datefact) as year, month(datefact) as month, sum(mnt_facture) as total
from hccfact 
where year(datefact)  = '2015'
group by succ, year(datefact) , month(datefact)

Open in new window


The reason for the same that having is applied when the data is pulled out and then limit the number of rows, However where is applied while your pulling the data itself on the first place...

Saurabh...
0
Éric MoreauSenior .Net ConsultantCommented:
The only way (I think) would be to use a sub-query;

select succ, year, month, sum(mnt_facture) as total 
from (
   select succ, year(datefact) as year, month(datefact) as month, mnt_facture
   from hccfact 
   WHERE year(datefact)  = '2015'
) as A
group by succ, year , month

Open in new window


You need to run both queries while showing the execution plan to see if this other syntax has an impact of the performance (which I doubt)
0
Buropro-CitationAuthor Commented:
Thank you for the alternative, I'll try this.  To your knowledge, should I minimize the use of functions (like month, case, cast, datediff and so on), does this really impact on the speed of a query?

I'm asking this because in a recent post I was told that using something like CONVERT(VARCHAR(10),datefact, 121) is pretty bad and should not be use, I'm not an sql expert yet, I know my way around but I still don't know the pitfall I should be avoiding in sql queries for optimization.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Éric MoreauSenior .Net ConsultantCommented:
Converting to char is more complex than getting the Year or Month.

It is true that limiting the use of functions help performance so you need to choose them wisely. Limiting the number of rows is also you best friend.

When trying to find out if syntax changes anything performance-wise, I always create 2 statements in the same query window (the original statement and the new statement). I execute both at the same time while the Execution Plan is on. I then check the results of the execution plan to see if both statements provides the same performance.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Buropro-CitationAuthor Commented:
Problem is that I don't have access to the execution plan since I'm working in cooperation with a company that have installed a software that uses SQL here and they are pretty hesitant on giving rights.   Either way, these are nice suggestions, I wasn't aware that we could see the execution plan.

thank you :)
0
PortletPaulfreelancerCommented:
:(
don't run functions of every row of date to suit a single parameter
instead change the parameter(s) to suit the data

In this case you can (and should) avoid using YEAR() in the where clause
instead this would be a "sargable" (can use an index)

for mssql:
WHERE datefact > = '20150101' AND datefact < '20160101'

for oracle:
WHERE datefact > = to_date('20150101','yyyymmdd') AND datefact < to_date('20160101','yyyymmdd')

(not sure what dbms is being used)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.