How to display rows that are missing from aggregations?

Question

I create a view to display ValueRange like this:

...
ValueRange=case when Value1>60  and Value1<=65  then '60-65'
                when Value1>65  and Value1<=70  then '65-70'
                when Value1>70  and Value1<=75  then '70-75' END
...

But I don't have any data for Value1>65 and Value1<=70... so I can't see the these rows in result.

My result is :

ValueRange   Count
60-65          5
70-75          3

But my result should be:

ValueRange   Count
60-65          5
65-70          0
70-75          3

Edit:As i said before, full code is more complex. Actually this is the two dimensional pivot table and your answers are invalid for my sql. So i publish all codes. WITH RangeTable(YukRange,FiyatRange) AS (SELECT YukRange=case when Yük<=20000 or Yük IS NULL then '0-20000' when Yük>20000 and Yük<=21000 then '20000-21000' when Yük>21000 and Yük<=22000 then '21000-22000' when Yük>22000 and Yük<=23000 then '22000-23000' when Yük>23000 and Yük<=24000 then '23000-24000' when Yük>24000 and Yük<=25000 then '24000-25000' when Yük>25000 and Yük<=26000 then '25000-26000' when Yük>26000 and Yük<=27000 then '26000-27000' when Yük>27000 and Yük<=28000 then '27000-28000' when Yük>28000 and Yük<=29000 then '28000-29000' when Yük>29000 and Yük<=30000 then '29000-30000' when Yük>30000 and Yük<=31000 then '30000-31000' when Yük>31000 and Yük<=32000 then '31000-32000' when Yük>32000 and Yük<=33000 then '32000-33000' when Yük>33000 and Yük<=34000 then '33000-34000' when Yük>34000 and Yük<=35000 then '34000-35000' when Yük>35000 and Yük<=36000 then '35000-36000' when Yük>36000 and Yük<=37000 then '36000-37000' when Yük>37000 and Yük<=38000 then '37000-38000' when Yük>38000 and Yük<=39000 then '38000-39000' when Yük>39000 and Yük<=40000 then '39000-40000' when Yük>40000 and Yük<=41000 then '40000-41000' END

       , FiyatRange=case when GunlukParams_SGOF<=60 or  GunlukParams_SGOF IS NULL then '0-60' 
                         when GunlukParams_SGOF>60  and GunlukParams_SGOF<=65  then '60-65'
                         when GunlukParams_SGOF>65  and GunlukParams_SGOF<=70  then '65-70'
                         when GunlukParams_SGOF>70  and GunlukParams_SGOF<=75  then '70-75'
                         when GunlukParams_SGOF>75  and GunlukParams_SGOF<=80  then '75-80'
                         when GunlukParams_SGOF>80  and GunlukParams_SGOF<=85  then '80-85'
                         when GunlukParams_SGOF>85  and GunlukParams_SGOF<=90  then '85-90'
                         when GunlukParams_SGOF>90  and GunlukParams_SGOF<=95  then '90-95'
                         when GunlukParams_SGOF>95  and GunlukParams_SGOF<=100 then '95-100'
                         when GunlukParams_SGOF>100 and GunlukParams_SGOF<=105 then '100-105'
                         when GunlukParams_SGOF>105 and GunlukParams_SGOF<=110 then '105-110'
                         when GunlukParams_SGOF>110 and GunlukParams_SGOF<=115 then '110-115'
                         when GunlukParams_SGOF>115 and GunlukParams_SGOF<=120 then '115-120'
                         when GunlukParams_SGOF>120 and GunlukParams_SGOF<=125 then '120-125'
                         when GunlukParams_SGOF>125 and GunlukParams_SGOF<=130 then '125-130'
                         when GunlukParams_SGOF>130 and GunlukParams_SGOF<=135 then '130-135'
                         when GunlukParams_SGOF>135 and GunlukParams_SGOF<=140 then '135-140'
                         when GunlukParams_SGOF>140 and GunlukParams_SGOF<=145 then '140-145'
                         when GunlukParams_SGOF>145 and GunlukParams_SGOF<=150 then '145-150'
                         when GunlukParams_SGOF>150 and GunlukParams_SGOF<=155 then '150-155'
                         when GunlukParams_SGOF>155 and GunlukParams_SGOF<=160 then '155-160'
                         when GunlukParams_SGOF>160 and GunlukParams_SGOF<=165 then '160-165'
                         when GunlukParams_SGOF>165 and GunlukParams_SGOF<=170 then '165-170'
                         when GunlukParams_SGOF>170 and GunlukParams_SGOF<=175 then '170-175'
                         when GunlukParams_SGOF>175 and GunlukParams_SGOF<=180 then '175-180'
                         when GunlukParams_SGOF>180 and GunlukParams_SGOF<=185 then '180-185'
                         when GunlukParams_SGOF>185 and GunlukParams_SGOF<=190 then '185-190'
                         when GunlukParams_SGOF>190 and GunlukParams_SGOF<=195 then '190-195'
                         when GunlukParams_SGOF>195 and GunlukParams_SGOF<=200 then '195-200'
                         when GunlukParams_SGOF>200 and GunlukParams_SGOF<=205 then '200-205'
                         when GunlukParams_SGOF>205 and GunlukParams_SGOF<=210 then '205-210'
                         when GunlukParams_SGOF>210 and GunlukParams_SGOF<=215 then '210-215'
                         when GunlukParams_SGOF>215 and GunlukParams_SGOF<=220 then '215-220'
                         when GunlukParams_SGOF>220 and GunlukParams_SGOF<=225 then '220-225'
                         when GunlukParams_SGOF>225 and GunlukParams_SGOF<=230 then '225-230'
                         when GunlukParams_SGOF>230 and GunlukParams_SGOF<=235 then '230-235'
                         when GunlukParams_SGOF>235 and GunlukParams_SGOF<=240 then '235-240'
                         when GunlukParams_SGOF>240 and GunlukParams_SGOF<=245 then '240-245'
                         when GunlukParams_SGOF>245 and GunlukParams_SGOF<=250 then '245-250'
                         when GunlukParams_SGOF>250 and GunlukParams_SGOF<=255 then '250-255'
                         when GunlukParams_SGOF>255 and GunlukParams_SGOF<=260 then '255-260'
                         when GunlukParams_SGOF>260 and GunlukParams_SGOF<=265 then '260-265'
                         when GunlukParams_SGOF>265 and GunlukParams_SGOF<=270 then '265-270'
                         when GunlukParams_SGOF>270 and GunlukParams_SGOF<=275 then '270-275'
                         when GunlukParams_SGOF>275 and GunlukParams_SGOF<=280 then '275-280'
                         when GunlukParams_SGOF>280 and GunlukParams_SGOF<=285 then '280-285' 
                         when GunlukParams_SGOF>285 and GunlukParams_SGOF<=290 then '285-290' 
                         when GunlukParams_SGOF>290 and GunlukParams_SGOF<=295 then '290-295'
                         when GunlukParams_SGOF>295 and GunlukParams_SGOF<=300 then '295-300'
                         when GunlukParams_SGOF>300                            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], [23000-24000], [24000-25000], [25000-26000], [26000-27000] , [27000-28000], [28000-29000], [29000-30000], [30000-31000], [31000-32000], [32000-33000], [33000-34000], [34000-35000] , [35000-36000], [36000-37000], [37000-38000], [38000-39000], [39000-40000], [40000-41000]) ) AS Pvt)

select * from MainTable;

Explain:I am getting the values from YukFiyat_View table. According to two values in this table i create temporary table for two different ranges. Original Table(YukFiyat_View): Date Yük GunlukParams_SGOF 2009-12-30 00:00:00.000 21590 140 2009-12-30 01:00:00.000 20208 100 2009-12-30 02:00:00.000 19313 70 2009-12-30 03:00:00.000 18866 57 2009-12-30 04:00:00.000 18812 57 2009-12-30 05:00:00.000 18974 58 2009-12-30 06:00:00.000 19791 69 2009-12-30 07:00:00.000 20630 69 2009-12-30 08:00:00.000 23314 69 2009-12-30 09:00:00.000 25318 69

My temporary table: Date Yük GunlukParams_SGOF YukRange FiyatRange 2009-12-30 00:00:00.000 21590 140 [21000-22000] [140-145] 2009-12-30 01:00:00.000 20208 100 [20000-21000] [100-105] 2009-12-30 02:00:00.000 19313 70 [0-20000] [70-75] 2009-12-30 03:00:00.000 18866 57 [0-20000] [0-60] 2009-12-30 04:00:00.000 18812 57 [0-20000] [0-60] 2009-12-30 05:00:00.000 18974 58 [0-20000] [0-60] 2009-12-30 06:00:00.000 19791 69 [0-20000] [65-70] 2009-12-30 07:00:00.000 20630 69 [20000-21000] [65-70] 2009-12-30 08:00:00.000 23314 69 [23000-24000] [65-70] 2009-12-30 09:00:00.000 25318 69 [25000-26000] [65-70] After that, I use pivot table to create two dimensional range table. Result is: FiyatRange [0-20000] [20000-21000] [21000-22000] ... [0-60] 3 0 0 [65-70] 1 1 0 [70-75] 1 0 0 [100-105] 0 1 0 [140-145] 0 0 1 . .

My problem is: Some ranges for FiyatRange doesn't contain in my table yet. Because of this, i cant see all FiyatRange values that i created doesn't display in result. So result is I want: FiyatRange [0-20000] [20000-21000] [21000-22000] ... [0-60] 3 0 0 [60-65] 0 0 0 [65-70] 1 1 0 [70-75] 1 0 0 [75-80] 0 0 0 . . [100-105] 0 1 0 . . [140-145] 0 0 1 . . So if the row value is all '0', i want to see this row in the result.

I'd suggest creating a derived table that contains all of the ValueRanges, then you can LEFT JOIN to make sure that you return all of the ranges. — Taryn, Aug 29 '14 at 14:39

Aaron Bertrand · Accepted Answer · 2014-08-29T19:32:02.347

12

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;

edited Aug 29 '14 at 19:32

answered Aug 29 '14 at 14:49

Aaron Bertrand

180,303
28
400
614

You could also replace the @ranges in the query with the values statement. eg FROM (VALUES (...),(...)) AS r (Label1, LBound, UBound) LEFT OUTER JOIN ... – MickyT Aug 29 '14 at 22:36
@MickyT yep, I'd rather go the other way though, if these ranges are always the ones used, why not simplify the query and put them in a real table? – Aaron Bertrand Aug 29 '14 at 22:40
@Aaron Bertrand Ranges is constant but the table that contains value1 is dynamic table(adding New values per an hour) So we cant use real table – Bünyamin Atik Aug 30 '14 at 23:40
@BünyaminAtik I'm not quite sure I understand the problem. You can certainly use a real table for the ranges, regardless of whether you left join that to a table variable, temporary table, or a "dynamic" table in dynamic SQL. In fact in the dynamic SQL case a permanent table for the ranges is going to make your life easier, not harder. – Aaron Bertrand Aug 30 '14 at 23:52
@Aaron Bertrand I added all codes and explain. please check it. Then lets talk about that – Bünyamin Atik Aug 31 '14 at 00:15
@Aaron Bertrand I have found a solution but query time is so long. I have changed a bit your answer and i solved but it takes 10 second to calculate result. But first result that i publish takes 1 second. I changed ranges table that includes only Label and changed JOIN like this: LEFT OUTER JOIN RangeTable AS o ON o.FiyatRange = r.Label – Bünyamin Atik Aug 31 '14 at 00:39

How to display rows that are missing from aggregations?

1 Answers1