CODEWITHSUNDEEP
ms-accessmedian
ac85
ac85

Reputation: 1

Median Calculation in Access with SQL

I am having a little difficulty with the Access SQL Query below. It is returning -1 and 0. -1 tells me that there is a median. The zero is there is no median( that 0 is the correct value for that median).Any help with changing the query to show the actual median value. It would really help. Thanks. 

SELECT (((Sum(IIf([CALCAULATION]<=[CALCULATION],1,0)))>=(Count(*)/2)) And ((Sum(IIf([CALCULATION]>=[CALCULATION],1,0)))>=(Count(*)/2))) AS Median, 
Datatable.Measure
FROM Datatable
WHERE (((Datatable.Date) Between #1/1/2014# And #1/31/2014#) AND ((Datatable.NAME)="Test"))
GROUP BY Datatable.Measure
ORDER BY Datatable.Measure;

Upvotes: 0

Views: 1556

Answers (2)

HarveyFrench
HarveyFrench

Reputation: 4568

Because [CALCAULATION]=[CALCULATION] will always be true and because (SUM( 1) always = COUNT(*)

This 

      SELECT (((SUM( 1)
               )>=(COUNT(*)/2
               )

will evaluate to true

so 

      SELECT (((SUM( 1)
               )>=(COUNT(*)/2
               )
         )
         AND
         ((SUM( 1
              )
           )>=(COUNT(*)/2)
         )) AS Median

is the same as true and true which is true ie 1.

This is clearly not what you had in mind.

Here's a google search to help

of which I would recommend this link

Here is you SQL reformatted

  SELECT (((SUM( IIF([CALCAULATION]<=[CALCULATION],1,0))
           )>=(COUNT(*)/2
               )
         )
         AND
         ((SUM( IIF([CALCULATION]>=[CALCULATION],1,0)
              )
           )>=(COUNT(*)/2)
         )) AS Median
       , Datatable.Measure
    FROM Datatable
   WHERE (Datatable.Date BETWEEN #1/1/2014# AND #1/31/2014#
          AND 
          Datatable.NAME="Test"
         )
GROUP BY Datatable.Measure
ORDER BY Datatable.Measure;

PART 2

Here's some SQL you have used to create a median (see NameNeededHereIThink and my comments). You might also need to add a WHERE clause to limi the dates to one month as in your previous example. NOTE the tables MUST be the same (or contain the same data!)

 SELECT CALCULATION AS Median
        -- #HARVEY  You might want to add the next lines
        , a3.MEASURE
        , a3.CALCULATION

  FROM ( SELECT a1.MEASURE
              , a1.CALCULATION
              , COUNT(a1.CALCULATION) Rank
           FROM Datatable_CALCULATION a1
              , Datatable_CALCULATION a2   
                --#HARVEy a1 and a2 MUST be the same table!
          WHERE a1.CALCULATION < a2.CALCULATION
             OR (a1.CALCULATION=a2.CALCULATION
                 AND 
                 a1.MEASURE    <= a2.MEASURE)
       GROUP BY a1.MEASURE
              , a1.CALCULATION
       --#HARVEY REMOVED NEXT LINE
       -- as order by in sub query cannot be relied upon
       -- and was only added here for testing purposes
       -- ORDER BY a1.CALCULATION DESC
       )
       a3
 WHERE Rank =
       -- as 99/2 = 45.5 you may need to round down 
       -- tot the  nearest integer
       (SELECT cint( ((COUNT()) /2) ) AS NameNeededHereIThink
       -- (SELECT ((COUNT()+1) /2) 
          FROM Datatable_CALCULATION
       );

Upvotes: 1

HarveyFrench
HarveyFrench

Reputation: 4568

SELECT CALCULATION AS Median
     , a3.MEASURE
     , a3.CALCULATION
  FROM (  SELECT a1.MEASURE
              , a1.CALCULATION
              , COUNT(a1.CALCULATION) Rank
           FROM Datatable_CALCULATION a1
              , Datatable_0114_CALCULATION a2
          WHERE a1.CALCULATION < a2.CALCULATION
             OR (a1.CALCULATION=a2.CALCULATION
            AND a1.MEASURE    <= a2.MEASURE)
       GROUP BY a1.MEASURE
              , a1.CALCULATION
       )
       a3
 WHERE Rank =(
       (SELECT cint(((COUNT()) DIV 2)) AS Median
                  FROM[Datatable_CALCULATION]
               ));

Upvotes: 0

Related Questions