Count a query

[excalibur78]

I can't seem to get a record count of this select to work:

SELECT [Masterfile].[Store #], [Masterfile].[PC/LAN1 Start]
FROM [Masterfile]
LEFT JOIN [Inventory Tracking]
ON [Masterfile].[Store #] = [Inventory Tracking].[Store #]
WHERE [Masterfile].[PC/LAN1 Start] >= '4/29/2003' AND [Masterfile].[PC/LAN1 Start] <= GetDate() AND [Inventory Tracking].[BOM Ship Date] Is Null AND [Inventory Tracking].[Store #] Is Null and [Masterfile].[Store #] <> '1020' and [Masterfile].[Store #] <> '926' and [Masterfile].[Store #] <> '6314' and [Masterfile].[Store #] <> '1027'
ORDER BY [Masterfile].[PC/LAN1 Start]

Any ideas would be great.


Thanks,

David

 

[TomKane]

What is the database? I have found that for example Pervasive doesn't support the recordcount thing. Also how are you connecting to the database?

 

[ClaireHCM]

usually u need group by for counting.May I know which column you want to count.
Your query doesnt seem to have problem.Are you getting any error msgs?
What kind of result you want to get?

 

[excalibur78]

Anything will work for record count. [Masterfile].[Store #] would be the logical choice. What I was trying to do is somethink like this:

Select Count(*) from (SELECT [Masterfile].[Store #], [Masterfile].[PC/LAN1 Start]
FROM [Masterfile]
LEFT JOIN [Inventory Tracking]
ON [Masterfile].[Store #] = [Inventory Tracking].[Store #]
WHERE [Masterfile].[PC/LAN1 Start] >= '4/29/2003' AND [Masterfile].[PC/LAN1 Start] <= GetDate() AND [Inventory Tracking].[BOM Ship Date] Is Null AND [Inventory Tracking].[Store #] Is Null and [Masterfile].[Store #] <> '1020' and [Masterfile].[Store #] <> '926' and [Masterfile].[Store #] <> '6314' and [Masterfile].[Store #] <> '1027'
ORDER BY [Masterfile].[PC/LAN1 Start])

 

[AtomicWedgie]

Shouldn't it be something like this????
(Don't know what you need the left join for)

Select Count(*)
FROM [Masterfile] LEFT JOIN [Inventory Tracking]
ON [Masterfile].[Store #] = [Inventory Tracking].[Store #]
WHERE [Masterfile].[PC/LAN1 Start] >= '4/29/2003'
AND [Masterfile].[PC/LAN1 Start] <= GetDate()
AND [Inventory Tracking].[BOM Ship Date] Is Null
AND [Inventory Tracking].[Store #] Is Null
AND [Masterfile].[Store #] <> '1020'
OR [Masterfile].[Store #] <> '926'
OR [Masterfile].[Store #] <> '6314'
OR [Masterfile].[Store #] <> '1027'


I believe you should use OR instead of AND in your [Masterfile].[Store #]

Regards,

Atomic Wedgie

 

[ClaireHCM]

Maybe you should give some example.Because your query doesnt have any problem.Or you should explain more detailly about the condition and also the desired result

 

[excalibur78]

Thanks everyone. I modified Atomic's and think I got it working right.


Thanks,


David

 

[JamesLean]

Unless I misunderstand, this will do it:

SELECT COUNT(*)
FROM [Masterfile]
LEFT JOIN [Inventory Tracking]
ON [Masterfile].[Store #] = [Inventory Tracking].[Store #]
WHERE [Masterfile].[PC/LAN1 Start] >= '4/29/2003' AND [Masterfile].[PC/LAN1 Start] <= GetDate() AND [Inventory Tracking].[BOM Ship Date] Is Null AND [Inventory Tracking].[Store #] Is Null and [Masterfile].[Store #] <> '1020' and [Masterfile].[Store #] <> '926' and [Masterfile].[Store #] <> '6314' and [Masterfile].[Store #] <> '1027'

--James

 

[AtomicWedgie]

Did you use OR instead of AND in your [Masterfile].[Store #] or is AND the right way. I always get those two mixed up.

 

[excalibur78]

Final select was:

Select Count(*)
FROM [Masterfile] LEFT JOIN [Inventory Tracking]
ON [Masterfile].[Store #] = [Inventory Tracking].[Store #]
WHERE [Masterfile].[PC/LAN1 Start] >= '4/29/2003'
AND [Masterfile].[PC/LAN1 Start] <= GetDate()
AND [Inventory Tracking].[BOM Ship Date] Is Null
AND [Inventory Tracking].[Store #] Is Null
AND [Masterfile].[Store #] <> '1020'
AND [Masterfile].[Store #] <> '926'
AND [Masterfile].[Store #] <> '6314'
AND [Masterfile].[Store #] <> '1027'