December 13, 2007 at 10:04 pm
Hello everyone , me using SQL Server 2000, the tables structure are
Transactions:
1) TransactionID int (PK)
VehicleCategoryCode int(FK)
TollDiscountID int (FK)
PlazaCode int (FK)
ProjectCode int (FK)
ClientCode int (FK)
TransactionDate datetime
2) TollDiscounts
TollDiscountID int (PK)
DiscountValue numeric
IsActive smallint
3) TollingVehicleCategories
VehicleCategoryCode int (PK)
PlazaCode int (FK)
ProjectCode int (FK)
ClientCode int (FK)
VehicleVategoryName varchar(50)
IsActive smallint
Here is my query:
SELECT dbo.TollingVehicleCategories.TollingVehicleCategoryName, COUNT(dbo.Transactions.TransactionID) AS TotalVehicles
, case TollDiscountID when 4 then Count(TollDiscountID) end as VehiclesExempt
FROM dbo.Transactions INNER JOIN
dbo.TollingVehicleCategories ON dbo.Transactions.VehicleCategoryCode = dbo.TollingVehicleCategories.TollingVehicleCategoryCode AND
dbo.Transactions.PlazaCode = dbo.TollingVehicleCategories.PlazaCode AND
dbo.Transactions.ProjectCode = dbo.TollingVehicleCategories.ProjectCode AND
dbo.Transactions.ClientCode = dbo.TollingVehicleCategories.ClientCode
GROUP BY dbo.TollingVehicleCategories.TollingVehicleCategoryName,TollDiscountID
its shows the VehiclesCount per VehicleCategory e.g Cars 12, Truck 20 so on, now i want to show how many vehciles exempt per category vise
To check how many Vehicles Exempt if TollDiscountID from Transaction table = 4 then count that IDs but per category vise and for DiscountCharged if TollDiscountID other than 1,4 make a count and show per category wise.
The desired result is :
CategoryName TotalVehiclesVehiclesExemptDiscountCharged
car50544
Truck2500
so on......
but when i run the above query its not giving me the desired results results. Plz check it and help me, if i using sybquery then
it gives me msg subquery not return many rows coz i need per category wise these data.
December 14, 2007 at 5:34 am
I don't really understand what you have written but it sounds as if you want to use a case statement within a SUM.
SELECT SUM(CASE Transactions.TollDiscountID WHEN 4 THEN 1 ELSE 0 END) AS Exempt ,
SUM (CASE Transactions.TollDiscountID WHEN 4 THEN 0 ELSE 1 END) AS NotExempt
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply