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.
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.
The only way (I think) would be to use a sub-query;
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)
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 , monthYou 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)
ASKER
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),datefa
I'm asking this because in a recent post I was told that using something like CONVERT(VARCHAR(10),datefa
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 :)
thank you :)
:(
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','yyyymm
(not sure what dbms is being used)
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','yyyymm
(not sure what dbms is being used)
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...