T-SQL return values for each row even if 0
-
Hello All, (btw still new to t-sql)
I am trying to return values for each row in a grouped table. (not too sure if that takes sense)
I got a result set of over ~80k rows. Let say CityName, RegionName, Value, Date and so on, I need to group and display results per CityName (distinct) but treat that group of CityName as a fixed values (distinct). I mean, even if where clause is added and result set will not have value for a particular CityName that CityName is included with 0 value.
At the moment when I use where clause and value does not exist it is omitted from the result set. I code (CS/VB) I can use for each but not too sure if I can use something like that in t-sql.
Thank you and pls let me know if above is not explained clear enough
Kris
-
Given this sample data:
DECLARE @Data AS TABLE(
CityName varchar(50) NOT NULL,
RegionName varchar(50) NOT NULL,
Value money NOT NULL,
TheDate date NOT NULL
);
INSERT @Data
(CityName, RegionName, Value, TheDate)
VALUES
('Wellington', 'North Island', $205, '2012-02-01'),
('Auckland', 'North Island', $250, '2012-02-02'),
('Christchurch', 'South Island', $135, '2012-02-01'),
('Dunedin', 'South Island', $115, '2012-02-01');
This would be the old way:
-- Old syntax, deprecated
-- Do not use
SELECT
d.CityName,
TotalValue = SUM(Value)
FROM @Data AS d
WHERE
d.TheDate = '2012-02-01'
GROUP BY ALL
d.CityName;
And this is the new:
-- Equivalent query
SELECT
SubQuery.CityName,
TotalValue = SUM(SubQuery.Value)
FROM
(
SELECT
d.CityName, d.Value
FROM @Data AS d
WHERE
d.TheDate = '2012-02-01'
UNION ALL
SELECT d.CityName, Value = NULL
FROM @Data AS d
) AS SubQuery
GROUP BY
SubQuery.CityName;
Results:
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply