snippetModerate
How to display rows that are missing from aggregations?
Viewed 0 times
fromrowsmissingareaggregationsthathowdisplay
Problem
I create a view to display
But I don't have any data for
FROM YukFiyat_View),
MainTable AS (
SELECT * FROM RangeTable
PIVOT(
COUNT(YukRange)
FOR YukRange IN ([0-20000], [20000-21000], [21000-22000], [22000-23000], [
ValueRange like this:...
ValueRange=case when Value1>60 and Value165 and Value170 and Value1<=75 then '70-75' END
...But I don't have any data for
Value1>65 and `Value120000 and Yük21000 and Yük22000 and Yük23000 and Yük24000 and Yük25000 and Yük26000 and Yük27000 and Yük28000 and Yük29000 and Yük30000 and Yük31000 and Yük32000 and Yük33000 and Yük34000 and Yük35000 and Yük36000 and Yük37000 and Yük38000 and Yük39000 and Yük40000 and Yük<=41000 then '40000-41000' END, FiyatRange=case when GunlukParams_SGOF60 and GunlukParams_SGOF65 and GunlukParams_SGOF70 and GunlukParams_SGOF75 and GunlukParams_SGOF80 and GunlukParams_SGOF85 and GunlukParams_SGOF90 and GunlukParams_SGOF95 and GunlukParams_SGOF100 and GunlukParams_SGOF105 and GunlukParams_SGOF110 and GunlukParams_SGOF115 and GunlukParams_SGOF120 and GunlukParams_SGOF125 and GunlukParams_SGOF130 and GunlukParams_SGOF135 and GunlukParams_SGOF140 and GunlukParams_SGOF145 and GunlukParams_SGOF150 and GunlukParams_SGOF155 and GunlukParams_SGOF160 and GunlukParams_SGOF165 and GunlukParams_SGOF170 and GunlukParams_SGOF175 and GunlukParams_SGOF180 and GunlukParams_SGOF185 and GunlukParams_SGOF190 and GunlukParams_SGOF195 and GunlukParams_SGOF200 and GunlukParams_SGOF205 and GunlukParams_SGOF210 and GunlukParams_SGOF215 and GunlukParams_SGOF220 and GunlukParams_SGOF225 and GunlukParams_SGOF230 and GunlukParams_SGOF235 and GunlukParams_SGOF240 and GunlukParams_SGOF245 and GunlukParams_SGOF250 and GunlukParams_SGOF255 and GunlukParams_SGOF260 and GunlukParams_SGOF265 and GunlukParams_SGOF270 and GunlukParams_SGOF275 and GunlukParams_SGOF280 and GunlukParams_SGOF285 and GunlukParams_SGOF290 and GunlukParams_SGOF295 and GunlukParams_SGOF300 then '300+' ENDFROM YukFiyat_View),
MainTable AS (
SELECT * FROM RangeTable
PIVOT(
COUNT(YukRange)
FOR YukRange IN ([0-20000], [20000-21000], [21000-22000], [22000-23000], [
Solution
DECLARE @ranges TABLE
(
Label VARCHAR(20),
LBound INT,
UBound INT
);
INSERT @ranges(Label,LBound,UBound)
VALUES(...),
('60-65', 60, 65),
('70-75', 70, 75),
(...);
SELECT ValueRange = r.Label, [Count] = COUNT(o.key)
FROM @ranges AS r
LEFT OUTER JOIN dbo.othertable AS o
ON o.value1 > r.LBound
AND o.value1 <= r.UBound
-- AND other filters for dbo.othertable
GROUP BY r.Label;Code Snippets
DECLARE @ranges TABLE
(
Label VARCHAR(20),
LBound INT,
UBound INT
);
INSERT @ranges(Label,LBound,UBound)
VALUES(...),
('60-65', 60, 65),
('70-75', 70, 75),
(...);
SELECT ValueRange = r.Label, [Count] = COUNT(o.key)
FROM @ranges AS r
LEFT OUTER JOIN dbo.othertable AS o
ON o.value1 > r.LBound
AND o.value1 <= r.UBound
-- AND other filters for dbo.othertable
GROUP BY r.Label;Context
StackExchange Database Administrators Q#75320, answer score: 12
Revisions (0)
No revisions yet.