HAVING clause?

Hi all,

How would I add:

WHERE Year(tblDetails.DateAdded)=#2003#

... to the SQL statement below?

"SELECT tblDetails.ProductID, tblProducts.ShortDesc, Count(tblDetails.ProductID) AS ProductCount FROM (tblDetails INNER JOIN tblProducts ON tblDetails.ProductID = tblProducts.ProductID) GROUP BY tblDetails.ProductID, tblProducts.ShortDesc ORDER BY Count(tblDetails.ProductID) DESC"

Cheers,
Davidi think the order is

select...
from...
where...
group by ...
having...
order by...

sql

Having Clause wont run on Linked Server

I am trying to find all affiliates that have more commissions from this
week to the prior week. The problem is in the having part where
"aff2.affiliateid = aff.affiliateid". SQL Server just doesn't like
trying to use "aff.affiliateid" to refer to the parent table.

The error I get is "Invalid column name 'Col1010'.", but ONLY on the
sql01 box; it runs correctly on the box where the affiliate database
is, sql02.

If I get rid of the alias of "aff" and use the full path, I get the
error, "The number name 'sql02.affiliates.dbo.affiliates_sum' contains
more than the maximum number of prefixes. The maximum is 3."

Is this just a SQL bug, or is there a fix?

declare @.date datetime ; set @.date = '2/3/05'

select affiliateid
from sql02.affiliates.dbo.affiliates_sum aff
where day >= @.date - 7 and day < @.date
group by affiliateid
having sum(lead_commissions) > (
select sum(lead_commissions + sales_commissions) as total
from sql02.affiliates.dbo.affiliates_sum aff2
where day >= @.date - 14 and day < @.date - 7
and aff2.affiliateid = aff.affiliateid
group by affiliateid
)<scottelkin@.gmail.com> wrote in message
news:1108711888.673744.271790@.c13g2000cwb.googlegr oups.com...
>I am trying to find all affiliates that have more commissions from this
> week to the prior week. The problem is in the having part where
> "aff2.affiliateid = aff.affiliateid". SQL Server just doesn't like
> trying to use "aff.affiliateid" to refer to the parent table.
> The error I get is "Invalid column name 'Col1010'.", but ONLY on the
> sql01 box; it runs correctly on the box where the affiliate database
> is, sql02.
> If I get rid of the alias of "aff" and use the full path, I get the
> error, "The number name 'sql02.affiliates.dbo.affiliates_sum' contains
> more than the maximum number of prefixes. The maximum is 3."
> Is this just a SQL bug, or is there a fix?
>
> declare @.date datetime ; set @.date = '2/3/05'
> select affiliateid
> from sql02.affiliates.dbo.affiliates_sum aff
> where day >= @.date - 7 and day < @.date
> group by affiliateid
> having sum(lead_commissions) > (
> select sum(lead_commissions + sales_commissions) as total
> from sql02.affiliates.dbo.affiliates_sum aff2
> where day >= @.date - 14 and day < @.date - 7
> and aff2.affiliateid = aff.affiliateid
> group by affiliateid
> )

This KB article might be relevant:

http://support.microsoft.com/defaul...kb;en-us;825019

If that doesn't help, or if you need a workaround, you could try using
OPENQUERY() to pass the query through to sql02, although since you need to
include a parameter value, you would need dynamic SQL:

http://www.sommarskog.se/dynamic_sql.html#OPENQUERY

Simon|||(scottelkin@.gmail.com) writes:
> I am trying to find all affiliates that have more commissions from this
> week to the prior week. The problem is in the having part where
> "aff2.affiliateid = aff.affiliateid". SQL Server just doesn't like
> trying to use "aff.affiliateid" to refer to the parent table.
> The error I get is "Invalid column name 'Col1010'.", but ONLY on the
> sql01 box; it runs correctly on the box where the affiliate database
> is, sql02.
> If I get rid of the alias of "aff" and use the full path, I get the
> error, "The number name 'sql02.affiliates.dbo.affiliates_sum' contains
> more than the maximum number of prefixes. The maximum is 3."
> Is this just a SQL bug, or is there a fix?

Judging from the error message, this is a bug in either SQL Server
or SQLOLEDB.

I created a table from the query and then ran the query on my box
with a loopback linked server, and the query completed succesfully.
I am running the beta of SP4, so it could be because the issue have
been fixed. However, the problem may be related to a specific query
plan, so it is difficult to tell for sure.

Info about SP4 Beta is here: http://support.microsoft.com/kb/290211.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Having clause without GROUP BY clause?

Hi,

What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?

eg :

SELECT SUM(col1) from test HAVING col2 < 5

SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5

I want the equivalent query in MSSQLServer for the above Oracle query.

Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.

Thanks,
Gopi.those queries actually run in oracle? i rather doubt it

without a GROUP BY, the entire table is considered a single group

the individual col2 values would not necessarily all be the same, therefore the HAVING condition in the first query would not necessarily give you the results you want, assuming it even runs, which i doubt

in the second query you will surely get a syntax error even in oracle

perhaps what you want for the two queries is:

SELECT SUM(col1) from test where col2 < 5

SELECT SUM(col1) from test WHERE x=y AND col2 < 5|||Sorry for the typos. Actually the queries are as follows.

SELECT SUM(col1) from test HAVING SUM(col2) < 5

SELECT SUM(col1) from test WHERE x=y HAVING SUM(col2) < 5

Thanks,
Gopi.|||do you have sql server? if so, why don't you test those queries and see what you get

HAVING clause is a case statement??

i have wrote a query which compares two tables, returning anywhere the qty is not the same in each table:

(simple ex)

Select order_no
from table1
inner join table2
on table1.order_no = table2.order_no
group by order_no
having table1.Qty<> table2.Qty

BUT... I need to add a table3, where there maybe (or may not be enters - thus a left join). If there is an entry in table3 then use qty in table3 and not table1... so having becomes:

CASE WHEN table1.Qty<> table3.Qty
THEN table3.Qty<> table2.Qty
ELSE table1.Qty<> table2.Qty END

but how do i actually write this?perhaps if you would care to explain what you're doing?

are you comparing individual Qty values, or the SUMs?

because the HAVING clause may refer only to columns in the GROUP BY or to aggregate functions|||perhaps if you would care to explain what you're doing?

are you comparing individual Qty values, or the SUMs?

because the HAVING clause may refer only to columns in the GROUP BY or to aggregate functions

Sorry I am trying to compare Sum(qty) for each product in an order (product maybe in the order more than 1ce) I am trying to retrieve any product lines where Sum qties in table1 and table2 are not the same.

However, if stock was not found, then an allocated qty is recorded in table 3...so in this case I want to compare qtyies in table3 and table2
?|||select t1.order_no
, t1.sumqty
, t2.order_no
, t2.sumqty
from (
select order_no
, sum(qty) as sumqty
from table1
group
by order_no
) as t1
full outer
join (
select order_no
, sum(qty) as sumqty
from table2
group
by order_no
) as t2
on t2.order_no = t1.order_no
and t2.sumqty <> t1.sumqtythat's the general strategy -- do your sums in derived tables

for table 3, you're on your own :)

Having Clause in MDX

I have the following relationships,

DimTest > FactTestScores <- DimStudents

so if I have 5 tests, A, B, C, D, E

how do I write an MDX statement to get all students who have taken all 3 tests (not just one of the 3) A,B,C ?

pseudocode: something like this would work?

SELECT {} on 0, DimStudents.Members on 1

FROM CUBE

WHERE (A,B,C)

can I use HAVING CLAUSE anywhere to make this work?

http://www.biblogs.com/2006/01/26/the-having-clause/

thanks

Assuming that there is a "count" measure on FactTestScores like [TestCount]:

With Set [SelectedTests] as {A, B, C}

select {} on 0,

DimStudent.Student.Student.Members

Having Count(NonEmpty([SelectedTests],

{[Measures].[TestCount]})

= Count([SelectedTests] ) on 1

from CUBE

|||

thank you Deepak

Please remind me one of these days how I can get you a gift to thank you for all your help.

HAVING Clause has no effect

I have this stored procedure. I want to run a few simple SQL functions against my tables. In particular I want to take a subset of records (One or Two years worth) and calculate AVG, VAR and STDEV.

It does not work the way I thought it would. I end up with the whole input table in #tempor1 which is about 6 years worth of records.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF

GO
ALTER PROCEDURE [dbo].[findAve1YearDailyClose_MSFT]
AS
BEGIN
SET NOCOUNT ON;
SELECT adjClosed, volume INTO #tempor1 FROM dbo.dailyCl_MSFT
GROUP BY dateTimed, adjClosed, volume
HAVING (dateTimed > DATEADD (year, -1, MAX (dateTimed)))

SELECT AVG (adjClosed) AS "AVGAdjClose1Year",
VAR (adjClosed) AS "VARAdjClose1Year", AVG (volume) AS "AVGVolume1Year",
STDEV (volume) AS "STDEVVolume1Year", COUNT (*) AS "total"
FROM #tempor1
END

Thus if I change the number of years I subtract from the latest date from 1 to 2 I end up with the same result. What is the problem?

Thanks.

What about using:

SELECT adjClosed, volume INTO #tempor1
FROM dbo.dailyCl_MSFT
WHERE dateTimed > (SELECT DATEADD(year, -1, MAX (dateTimed)) FROM dbo.dailyCl)
GROUP BY dateTimed, adjClosed, volume

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

What about using:

SELECT adjClosed, volume INTO #tempor1
FROM dbo.dailyCl_MSFT
WHERE dateTimed > (SELECT DATEADD(year, -1, MAX (dateTimed)) FROM dbo.dailyCl)
GROUP BY dateTimed, adjClosed, volume

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

It sure worked! Many thanks for a lesson. Marked as answered!

Thanks.

sql

having clause and where clause

for the following SQL statement
select ...
group by col1
having col1 is not null
Does it always return the same result as
select ...
where col1 is not null
group by col1
?
The "where" one should have better performance, is it always true?Having gets evaluated after all the results have been returned and
aggregates calculated.
Where gets evaluated as the results are returned. At which point the filter
happens depends on the execution plan, but it will always be prior to
aggregation.
As a rule, only criteria on aggregate columns (sum, count, etc.) should be
in the having clause. Everything else belongs in the where.
Where SHOULD always be more efficient than having. HOWEVER, it is possible
(but highly unlikely) that the difference could cause SQL Server to use a
different execution plan that by some freak coincidence would perform better
with the having than the where. This would be the exception, and I would be
rather surprised if anyone could come up with such a case.
"nick" <nick@.discussions.microsoft.com> wrote in message
news:D3E95AF6-9550-4842-A62E-9B78A93AFC19@.microsoft.com...
> for the following SQL statement
> select ...
> group by col1
> having col1 is not null
> Does it always return the same result as
> select ...
> where col1 is not null
> group by col1
> ?
> The "where" one should have better performance, is it always true?|||They should return the same result. However, the version with the where
clause is (IMHO) better code. Where should be used when you want to compare
on the values in each row, use haveing when you want to compare on
aggregated values. So the SQL to retrive all customers with orders totaling
more than $1000 from New York would be something like:
Select ...
Where State = 'NY'
Group By CustomerID
Having Amount > 1000
Tom
"nick" <nick@.discussions.microsoft.com> wrote in message
news:D3E95AF6-9550-4842-A62E-9B78A93AFC19@.microsoft.com...
> for the following SQL statement
> select ...
> group by col1
> having col1 is not null
> Does it always return the same result as
> select ...
> where col1 is not null
> group by col1
> ?
> The "where" one should have better performance, is it always true?|||Use having ONLY if you cannot achieve the functionality through the where
clause.
In case of having the result set is already generated and the filter is
applied.
And in this particular case that you have mentioned there won't be any
difference in the result (or so I think).

Hand needed with t-sql

Is it possible to write these two blocks of code as one? The only
difference between them is the AND clause: AND is_trade_date = 1 versus AND
is_sett_date = 1.

Cheers,

David

IF (@.trade_dates = 1)
BEGIN
IF EXISTS (SELECT 1 FROM calendar
WHERE calendar_date = @.date
AND is_trade_date = 1)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END
END
ELSE
BEGIN
IF EXISTS (SELECT 1 FROM calendar
WHERE calendar_date = @.date
AND is_sett_date = 1)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END
END
ENDDavid (auto87829@.hushmail.com) writes:
> Is it possible to write these two blocks of code as one? The only
> difference between them is the AND clause: AND is_trade_date = 1 versus
> AND is_sett_date = 1.

If my guess of the logic is right:

IF EXISTS (SELECT 1
FROM calendar
WHERE calendar_date = @.date
AND (is_trade_date = 1 OR @.trade_dates = 0)
AND (is_sett_date = 1 OR @.trade_dates = 1)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Why not:
IF (@.trade_dates = 1)
BEGIN
IF EXISTS (SELECT 1 FROM calendar
WHERE calendar_date = @.date AND
(is_trade_date = 1 OR is_sett_date = 1)
)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END
END

Ricardo

"David" <auto87829@.hushmail.com> wrote in message news:<3fece73f$0$18747$afc38c87@.news.optusnet.com.au>...
> Is it possible to write these two blocks of code as one? The only
> difference between them is the AND clause: AND is_trade_date = 1 versus AND
> is_sett_date = 1.
> Cheers,
> David
> IF (@.trade_dates = 1)
> BEGIN
> IF EXISTS (SELECT 1 FROM calendar
> WHERE calendar_date = @.date
> AND is_trade_date = 1)
> BEGIN
> SELECT @.day_cnt = @.day_cnt + 1
> END
> END
> ELSE
> BEGIN
> IF EXISTS (SELECT 1 FROM calendar
> WHERE calendar_date = @.date
> AND is_sett_date = 1)
> BEGIN
> SELECT @.day_cnt = @.day_cnt + 1
> END
> END
> END|||The following block should do the trick:
IF EXISTS ( SELECT 1
FROM calendar
WHERE calendar_date = @.date AND
(
( is_trade_date = 1 AND
@.trade_dates = 1
)
OR
( is_sett_date = 1 AND
@.trade_dates <> 1
)
)
)
BEGIN
SELECT @.day_cnt = @.day_cnt + 1
END

although not very elegant...

Ricardo.
"David" <auto87829@.hushmail.com> wrote in message news:<3fece73f$0$18747$afc38c87@.news.optusnet.com.au>...
> Is it possible to write these two blocks of code as one? The only
> difference between them is the AND clause: AND is_trade_date = 1 versus AND
> is_sett_date = 1.
> Cheers,
> David
> IF (@.trade_dates = 1)
> BEGIN
> IF EXISTS (SELECT 1 FROM calendar
> WHERE calendar_date = @.date
> AND is_trade_date = 1)
> BEGIN
> SELECT @.day_cnt = @.day_cnt + 1
> END
> END
> ELSE
> BEGIN
> IF EXISTS (SELECT 1 FROM calendar
> WHERE calendar_date = @.date
> AND is_sett_date = 1)
> BEGIN
> SELECT @.day_cnt = @.day_cnt + 1
> END
> END
> END