问题描述:

Here is my sql fiddle http://sqlfiddle.com/#!2/9e6691/3

[Quater 1: 01]

[Quater 2: 04]

[Quater 3: 07]

[Quater 4: 10]

I am working on a Google chart where i am creating chart for separate projects. Chart consists two lines in that. One line will mention the ratepersquarefeet of that project. Other line want to get the average values with respect to the quater and the common location.

Coding follows:

CREATE TABLE IF NOT EXISTS `ratepersqft` (

`price_id` int(11) NOT NULL auto_increment,

`project_id` int(11) default NULL,

`date` varchar(255) default NULL,

`rate_per_sqft` double default NULL,

`common_location` varchar(255) default NULL,

PRIMARY KEY (`price_id`));

INSERT INTO `ratepersqft` (`price_id`, `project_id`, `date`, `rate_per_sqft`, `common_location`) VALUES

(1, 26, '2012-01-17 ', 2800, 'ECR'),

(2, 54, '2012-04-17 ', 1550, 'ECR'),

(3, 53, '2012-07-17 ', 1850, 'ECR'),

(4, 58, '2012-10-17 ', 4425, 'ECR'),

(5, 85, '2012-01-17 ', 0, 'ECR'),

(6, 114, '2012-04-17 ', 2650, 'ECR'),

(7, 131, '2012-07-17 ', 2400, 'ECR'),

(8, 134, '2012-10-17 ', 2900, 'ECR'),

(9, 140, '2012-01-17 ', 2500, 'ECR'),

(10, 368, '2012-04-17 ', 1650, 'ECR'),

(11, 369, '2012-07-17 ', 1800, 'ECR'),

(12, 370, '2012-10-17 ', 2000, 'ECR'),

(13, 26, '2012-04-17 ', 2800, 'ECR'),

(14, 26, '2012-07-17 ', 2800, 'ECR'),

(15, 26, '2012-10-17 ', 2800, 'ECR');

For eg. in the fiddle i took project id 26 its common location in ECR

first line in chart will come with respect to the ratepersquarefeet in that project. There are some other projects which have ECR as a common location. I need to calculate an average value for those project with respect to the mentioned date.

For Eg. price_id: 1,5,9 the rate per sq feet are 2800,0,2500 The average of First Quater is 1766. Like wise i need to calculate for each quater.

网友答案:
SELECT ROUND(avg(rate_per_sqft)) as RatePerSqFt 
  FROM ratepersqft
 WHERE common_location = 'ECR' AND
       1 <= MONTH(date) AND MONTH(date) <3  
       group by common_location
UNION
SELECT ROUND(avg(rate_per_sqft)) as RatePerSqFt 
  FROM ratepersqft
 WHERE common_location = 'ECR' AND
       3 <= MONTH(date) AND MONTH(date)<6  
       group by common_location
UNION
SELECT ROUND(avg(rate_per_sqft)) as RatePerSqFt 
  FROM ratepersqft
 WHERE common_location = 'ECR' AND
       6 <= MONTH(date) AND MONTH(date)<9  
       group by common_location
UNION
SELECT ROUND(avg(rate_per_sqft)) as RatePerSqFt 
  FROM ratepersqft
 WHERE common_location = 'ECR' AND
       9 <= MONTH(date) AND MONTH(date)<=12
       group by common_location;  
网友答案:

You can do this by computing the averages by quarter/location in a subselect and then joining again against the table.

This is the query I came up with:

select * 
from ratepersqft r
join (
    SELECT 
      quarter(`date`) as quarter, 
      year(`date`) as year, 
      avg(rate_per_sqft) as QuarterAverage,
      common_location
    FROM ratepersqft
    group by 
      quarter(`date`), 
      year(`date`),
      common_location
  ) averages
  on year(r.`date`) = averages.year
  and quarter(r.`date`) = averages.quarter
  and r.common_location = averages.common_location

WHERE project_id = 26

SQL Fiddle: http://sqlfiddle.com/#!2/9e6691/23

网友答案:

?

Isn't the accepted answer just a convoluted way of saying this:

SELECT QUARTER(date) dt
     , common_location
     , ROUND(avg(rate_per_sqft)) as RatePerSqFt 
  FROM ratepersqft
 GROUP 
    BY dt
     , common_location  ;

?

相关阅读:
Top