HiveBrain v1.2.0
Get Started
← Back to all entries
snippetModerate

How to display rows that are missing from aggregations?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
fromrowsmissingareaggregationsthathowdisplay

Problem

I create a view to display 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+'    END


FROM 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.