August 18, 2009 at 3:30 am
Hi,
How can I use an alias in a where cluase. I have the following query
SELECT COUNT(dbo.tblBackup.BackupID) AS Total, dbo.tblClient.ClientName
FROM dbo.tblClient INNER JOIN
dbo.tblBackup ON dbo.tblClient.ClientID = dbo.tblBackup.ClientID
WHERE (dbo.tblBackup.BackupDate BETWEEN '2009-08-17 19:00:00' AND '2009-08-18 09:50:00') AND (Total = 0)
When I run it it says Total is not a valid column. How can I do this?
Cheers,
Billy
August 18, 2009 at 3:42 am
Logically the main clauses of a SELECT statement are evaluated in the following order:
FROM
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
ie WHERE is evaluated before Total is defined so the only way to use it is via a derived table:
SELECT *
FROM
(
SELECT COUNT(dbo.tblBackup.BackupID) AS Total
,dbo.tblClient.ClientName
FROM dbo.tblClient
INNER JOIN dbo.tblBackup
ON dbo.tblClient.ClientID = dbo.tblBackup.ClientID
WHERE dbo.tblBackup.BackupDate BETWEEN '20090817 19:00:00' AND '20090818 09:50:00'
GROUP BY dbo.tblClient.ClientName
) D
WHERE Total = 0
August 18, 2009 at 3:48 am
You cant as the return columns are processed after the join and filter conditions
You can do...
SELECT COUNT(dbo.tblBackup.BackupID) AS Total, dbo.tblClient.ClientName
FROM dbo.tblClient INNER JOIN
dbo.tblBackup ON dbo.tblClient.ClientID = dbo.tblBackup.ClientID
WHERE (dbo.tblBackup.BackupDate BETWEEN '2009-08-17 19:00:00' AND '2009-08-18 09:50:00')
Having COUNT(dbo.tblBackup.BackupID) =0
However that will still not work , or at least not return what you expect.
If you think about it an inner join has to join to something , but you want those where it hasnt joined.
So Try
SELECT dbo.tblClient.ClientName
FROM dbo.tblClient outer JOIN
dbo.tblBackup ON dbo.tblClient.ClientID = dbo.tblBackup.ClientID
and dbo.tblBackup.BackupDate BETWEEN '2009-08-17 19:00:00' AND '2009-08-18 09:50:00'
where dbo.tblBackup.ClientID is null
August 18, 2009 at 4:36 am
Dave Ballantyne (8/18/2009)
SELECT COUNT(dbo.tblBackup.BackupID) AS Total, dbo.tblClient.ClientNameFROM dbo.tblClient INNER JOIN
dbo.tblBackup ON dbo.tblClient.ClientID = dbo.tblBackup.ClientID
WHERE (dbo.tblBackup.BackupDate BETWEEN '2009-08-17 19:00:00' AND '2009-08-18 09:50:00')
Having COUNT(dbo.tblBackup.BackupID) =0
However that will still not work , or at least not return what you expect.
Dave - why do you think that this will not work?
This is exactly what I would have suggested for the OP.
Kev
August 18, 2009 at 4:50 am
Dave - why do you think that this will not work?
Because the inner join will fail ,ie join to no rows therefore, no rows will be returned to count to 0.
Not a very good explanation :ermm: ... try this code. The join , correctly , dosent return any rows , it dosent return "gjgjgjgjg , 0 " as the simple count does
create table #test(
test varchar(20))
go
insert into #test values('gjgjgjgjg')
go
select count(*) from sysobjects where name = 'gjgjgjgjg'
go
select #test.test ,count(*) from #test join sysobjects on sysobjects.name = #test.test
group by #test.test
August 18, 2009 at 5:09 am
Dave - why do you think that this will not work?
I think Dave is a bit cross-eyed this morning! 🙂
The OP is JOINing on ClientID but COUNTing BackupID. This could contain NULLs producing an COUNT of zero.
Also, the OPs query needs a GROUP BY dbo.tblClient.ClientName to make sense.
(I originally missed that until I had drunk some coffee!)
HAVING is the normal way of getting the result.
Using a derived table answers the question about why an alias does not work.
August 18, 2009 at 5:16 am
I think Dave is a bit cross-eyed this morning! 🙂
The OP is JOINing on ClientID but COUNTing BackupID. This could contain NULLs producing an COUNT of zero.
Its afternoon in London so well up to speed 🙂
If it were an outer join this yes BUT , NULLS still count in a COUNT
select #test.test ,count(*) from #test left join sysobjects on sysobjects.name = #test.test
group by #test.test
Another option could be to use sum(case when name is not null then 1 else 0 end)
August 18, 2009 at 5:25 am
NULLS still count in a COUNT
Umm... not according to my understanding.
DECLARE @t TABLE
(
Col int
)
INSERT INTO @t
SELECT NULL UNION ALL
SELECT 1 UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL
SELECT COUNT(Col)
FROM @t
The above produces a COUNT of 1, not 5, for me.
August 18, 2009 at 5:35 am
Ok , ill back track a bit :Whistling:
I was using count(*) in my testing not count(columnname) , which as you point out does not count nulls.
But it still needs to be an outer join not an inner join.
August 18, 2009 at 5:42 am
Dave - I get it now!
Couldn't see the wood for the trees, and thought it was a simple 'HAVING' answer!!
some sample data.....
declare @tblClient table (Clientid int, clientName varchar(10))
insert into @tblClient
select 1,'Alice'
union select 2,'Bob'
union select 3,'Claire'
union select 4,'Dave'
declare @tblBackup table (BackupID int, Clientid int, backupdate datetime)
insert into @tblBackup
select 1,1, '17 aug 2009 20:00'
union select 2,1, '17 aug 2009 20:00'
union select 3,1, '17 aug 2009 20:00'
union select 4,3, '17 aug 2009 20:00'
union select 5,3, '17 aug 2009 20:00'
union select 6,4, '17 aug 2009 20:00'
So if we use an OUTER join and add an optional WHERE clause...
SELECT
COUNT(tblBackup.BackupID) AS Total,
tblClient.ClientName
FROM
@tblClient tblClient
left JOIN@tblBackup tblBackup ON tblBackup.ClientID = tblClient.ClientID
WHERE
(tblBackup.BackupDate BETWEEN '2009-08-17 19:00:00' AND '2009-08-18 09:50:00' or tblBackup.BackupDate is null)
group by
tblClient.ClientName
Having
COUNT(tblBackup.BackupID) =0
gives us
Total ClientName
0 Bob
August 18, 2009 at 6:19 am
thank you all for your suggestions! this one is the closest to work. It displays the correct data but not all of it. i'll try figure out what exactly its doing
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply