Need some help with this COUNT SQL statement .. :)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need some help with this COUNT SQL statement .. :)

Author

Topic

davef101
Starting Member

5 Posts

Posted - 2008-02-02 : 05:06:08

ALTER PROCEDURE dbo.GetShippingZones

@ShippingServiceId int
AS

SELECT
RateCount.RateCount,
RateCount.ZoneId,
RateCount.Zone,
RateCount.PriceModifier,
CountryCount.CountryCount

FROM
(
SELECT

RateCount = COUNT(R.ZoneId),
Z.ZoneId,
Z.Zone,
Z.PriceModifier

FROM ShippingZones Z
LEFT JOIN ShippingRates R ON R.ZoneId = Z.ZoneId WHERE ShippingServiceId = @ShippingServiceId

GROUP BY Z.ZoneId,
Z.Zone,
Z.PriceModifier

) AS RateCount,

( SELECT CountryCount = COUNT(S.ZoneId)

FROM ShippingZones Z
LEFT JOIN ShippingCountryToZone S ON S.ZoneId = Z.ZoneId

) AS CountryCount

Basically i have 3 tables.
Table1 (ShippingZones)
Table2 (ShippingRates)
Table3 (ShippingCountriesToZone)
all linked.

This SQL Query is trying to count the amount of Rates and Countries with the same ZoneId as each row of the ShippingZones Table, so i can disable the delete button.

The result is, the amount of Countries is the same for each row. Its calculating the first row fine, then remembering it for all others ....

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-02 : 11:57:47

change select like this and try

SELECT 
RateCount.RateCount,
RateCount.ZoneId,
RateCount.Zone,
RateCount.PriceModifier,
CountryCount.CountryCount

FROM
(
SELECT

COUNT(R.ZoneId) AS RateCount,
Z.ZoneId,
Z.Zone,
Z.PriceModifier
FROM ShippingZones Z
LEFT JOIN ShippingRates R ON R.ZoneId = Z.ZoneId WHERE ShippingServiceId = @ShippingServiceId


GROUP BY Z.ZoneId,
Z.Zone,
Z.PriceModifier

)RateCount
INNER JOIN 
( SELECT S.ZoneId ,COUNT(S.ZoneId) AS CountryCount
FROM ShippingZones Z
LEFT JOIN ShippingCountryToZone S ON S.ZoneId = Z.ZoneId

)CountryCount
ON CountryCount.ZoneId =RateCount.ZoneId

davef101
Starting Member

5 Posts

Posted - 2008-02-02 : 16:03:13

Thanks for your reply :)

That only returns 1 result from the table??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources