Solved

Query syntax

Posted on 2014-10-14
3
74 Views
Last Modified: 2014-10-28
Hello,

Can someone explain the different between the two lines of code marked below in terms of the way the grouping is:


with cte as (
 select 
  -1 as kli_kliniknr,
  max(m.MeasurementDate) as MeasurementDate, 
  SUM(NumberOfMeasurements) as NumberOfMeasurements,
  SUM(NumberOfMen) as NumberOfMen,
  SUM(NumberOfWomen) as NumberOfWomen,

  -- What is the difference between these two rows?
  avg( Q1 * NumberOfMeasurements / NumberOfMeasurements) as Q1,
  sum( Q1 * NumberOfMeasurements) / sum(NumberOfMeasurements) as Q1x,

from [BPSD].[dbo].[MeasurementStatisticsPerUnit] m
group by m.MeasurementDate )
 
select * from cte ;

Open in new window

0
Comment
Question by:soozh
3 Comments
 
LVL 22

Assisted Solution

by:plusone3055
plusone3055 earned 167 total points
ID: 40380750
-- What is the difference between these two rows?
  avg( Q1 * NumberOfMeasurements / NumberOfMeasurements) as Q1,
  sum( Q1 * NumberOfMeasurements) / sum(NumberOfMeasurements) as Q1x,

Row 1 is AVERAGING
Row2 is SUMING

Row1  AVERAGE (Q1 * number of measurements / number of mesaurements
Row2  (its adding all together)  SUM(q1 * numberof measurements / SUM(numberof measurements)
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 166 total points
ID: 40380788
The AVG line is simply taking the average of the values of Q1.  The NumberOfMeasurements/Number of Measurements aspect of that element cancel each other out.

The Sum line looks like it is actually taking what most analysts would call the "weighted average".  This is determined by taking the sum of a value times the number of occurances of that value, and then dividing by the total number of measurements (the Sum(NumberOfMeasurements) figure).  So, if your table or query contains data like:

Q1     NumOfMeas
5                2
6                3
7               4

The first line would compute to the average of three elements)
AVG(5*2/2 , 6*3/3, 7*4/4) = 6

But the second method would compute as:
SUM(5*2, 6*3, 7*4)/Sum(2, 3, 4) = (10+18+28)/9 = 56/9 = 6.2222.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 167 total points
ID: 40380924
The first one is a simple average of Q1 ["* NumberOfMeasurements / NumberOfMeasurements" will always cancel each other out]

The second one, as Dale noted, is a different type of average, and could yield results quite different from the simple average.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now