March 30, 2009 at 4:00 pm
Hi guys,
Self teaching SQL here. Trying to get a list of invoice totals if the total is over 10000
I have this:
select weekno,sum(gross) as invtotal from invoices
where invtotal > 10000
group by weekno
order by weekno
but doesn't work !
Any help greatly appreciated.
Matt
March 30, 2009 at 4:08 pm
Try this:
select
weekno,
sum(gross) as invtotal
from
invoices
group by
weekno
having
sum(gross) > 10000
order by
weekno
March 30, 2009 at 5:01 pm
Nice one.
Looks like I shall be researching the 'HAVING' function.
Thanks,
Matt
March 30, 2009 at 6:44 pm
Hey, Lynn got to use HAVING! Wohoo! 😛
I think that we ought to award a prize anytime someone gets to use HAVING legitimately. 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 30, 2009 at 10:17 pm
RBarryYoung (3/30/2009)
Hey, Lynn got to use HAVING! Wohoo! 😛I think that we ought to award a prize anytime someone gets to use HAVING legitimately. 😀
You know, if I didn't know better, I'd swear you are trying to be a bit sarcastic here. 😉 :w00t:
Using the HAVING just seemed to fit. 😛
March 30, 2009 at 10:50 pm
Hi,
Try this
select a.weekno,b.invtotal
from
invoices as a,
(
select weekno,sum(gross) as invtotal
from invoices
group by weekno
)as b
where
a.weekno = b.weekno
and b.invtotal > 10000
ARUN SAS
March 30, 2009 at 10:59 pm
arun.sas (3/30/2009)
Hi,Try this
select a.weekno,b.invtotal
from
invoices as a,
(
select weekno,sum(gross) as invtotal
from invoices
group by weekno
)as b
where
a.weekno = b.weekno
and b.invtotal > 10000
ARUN SAS
As this is SQL Server 2005, you really should write this as:
select
b.weekno,
b.invtotal
from
( select
weekno,
sum(gross) as invtotal
from
dbo.invoices
group by
weekno) as b
where
b.invtotal > 10000;
or
with WeeklySales (
weekno,
invtotal
) as (
select
weekno,
sum(gross)
from
dbo.invoices
)
select
weekno,
invtotal
from
WeeklySales
where
invtotal > 10000;
March 31, 2009 at 5:10 am
And it should be noted, that both of my rewrites using the derived table and CTE are equivalent to my original code using the HAVING clause.
Also, arun.sas, in your code you are adding additional work not needed with the inner join back to the original table.
March 31, 2009 at 7:39 am
Lynn Pettis (3/30/2009)
RBarryYoung (3/30/2009)
Hey, Lynn got to use HAVING! Wohoo! 😛I think that we ought to award a prize anytime someone gets to use HAVING legitimately. 😀
You know, if I didn't know better, I'd swear you are trying to be a bit sarcastic here. 😉 :w00t:
Using the HAVING just seemed to fit. 😛
Nope, I was serious. When I was first learning SQL, HAVING was easily the one clause that took me the longest to figure out. For some reason I just could not figure out why it was needed when WHERE seemed to work perfectly fine. Finally after about a week I had my "Ah-hah!" moment.
In the many years since, I have always been disappointed that the HAVING that I worked so hard to understand was so rarely ever needed. And CTE's have jsut made that even worse :-(.
So seriously, we should celebrate the slowly disappearing HAVING clause, because we may not have it some day! 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 31, 2009 at 7:50 am
RBarryYoung (3/31/2009)
Lynn Pettis (3/30/2009)
RBarryYoung (3/30/2009)
Hey, Lynn got to use HAVING! Wohoo! 😛I think that we ought to award a prize anytime someone gets to use HAVING legitimately. 😀
You know, if I didn't know better, I'd swear you are trying to be a bit sarcastic here. 😉 :w00t:
Using the HAVING just seemed to fit. 😛
Nope, I was serious. When I was first learning SQL, HAVING was easily the one clause that took me the longest to figure out. For some reason I just could not figure out why it was needed when WHERE seemed to work perfectly fine. Finally after about a week I had my "Ah-hah!" moment.
In the many years since, I have always been disappointed that the HAVING that I worked so hard to understand was so rarely ever needed. And CTE's have jsut made that even worse :-(.
So seriously, we should celebrate the slowly disappearing HAVING clause, because we may not have it some day! 🙂
Maybe I'll look at doing a million row test to compare the three (four if you include the inner join) query methods detailed in this thread to see which is "better". Execution plans would be the first thing to look at.
March 31, 2009 at 10:31 am
Hi
I agree with you guys on HAVING clause. It took long time even for me also to figure it out. Would there be any performance issue if we use the below inplace of HAVING?
[highlight=""]
select
b.weekno,
b.invtotal
from
( select
weekno,
sum(gross) as invtotal
from
dbo.invoices
group by
weekno) as b
where
b.invtotal > 10000;[/highlight]
Thanks -- Vijaya Kadiyala
March 31, 2009 at 10:39 am
The only way to know is to test the different options and check the statistic io, statistics time, executions plans, and anything else people may suggest. between them.
I'd recommend a million row table for the testing. I'm hoping to have some time later to do the testing if noone beats me to it.
April 1, 2009 at 8:28 am
Thats a good idea let me try it out...
Thanks -- Vijaya Kadiyala
April 1, 2009 at 11:16 am
Guys,
I use HAVING all the time. It is alive and well in my code. I'd like someone to find a simpler way to get a list of customers that have 3 or more orders that aren't for free brochures than what I use:
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Orders
WHERE SubTotal > 0
GROUP BY CustomerID
HAVING COUNT(*) > 3
Todd Fifield
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply