Project

General

Profile

« Previous | Next » 

Revision 47350

Added by Tsampikos Livisianos almost 7 years ago

fix datasource charts query

View differences:

stats_db_schema.sql
907 907 
BEGIN
908 908 

  		




  909
  909
  
    
-- datasource charts


  		




  910
  
  
    
SELECT rd.datasource, rc.type, count(distinct rd.id) INTO "shadow".chart_datasource_type FROM "shadow".result_datasources rd, "shadow".result_classifications rc WHERE rd.id=rc.id GROUP BY rd.datasource, rc.type;


  		




  911
  
  
    
SELECT rd.datasource, r.year, count(distinct rd.id) INTO "shadow".chart_datasource_year FROM "shadow".result r, "shadow".result_datasources rd WHERE rd.id=r.id GROUP By rd.datasource, r.year;


  		




  912
  
  
    
SELECT rd.datasource, p.funder, count(distinct rd.id) INTO "shadow".chart_datasource_funders FROM "shadow".result_datasources rd, "shadow".project p, "shadow".project_results pr WHERE p.id=pr.id AND pr.result=rd.id GROUP BY rd.datasource, p.funder;


  		




  913
  
  
    
SELECT rd.datasource, p.title, count(distinct rd.id) INTO "shadow".chart_datasource_projects FROM "shadow".result_datasources rd, "shadow".project p, "shadow".project_results pr WHERE p.id=pr.id AND pr.result=rd.id GROUP BY rd.datasource, p.title;


  		




  
  910
  
    
CREATE TABLE "shadow".chart_datasource_type AS SELECT rd.datasource, rc.type, count(distinct rd.id) FROM "shadow".result_datasources rd, "shadow".result_classifications rc WHERE rd.id=rc.id GROUP BY rd.datasource, rc.type;


  		




  
  911
  
    
CREATE TABLE "shadow".chart_datasource_year AS SELECT rd.datasource, r.year, count(distinct rd.id) FROM "shadow".result r, "shadow".result_datasources rd WHERE rd.id=r.id GROUP By rd.datasource, r.year;


  		




  
  912
  
    
CREATE TABLE "shadow".chart_datasource_funders AS SELECT rd.datasource, p.funder, count(distinct rd.id) FROM "shadow".result_datasources rd, "shadow".project p, "shadow".project_results pr WHERE p.id=pr.id AND pr.result=rd.id GROUP BY rd.datasource, p.funder;


  		




  
  913
  
    
CREATE TABLE "shadow".chart_datasource_projects AS SELECT rd.datasource, p.title, count(distinct rd.id) FROM "shadow".result_datasources rd, "shadow".project p, "shadow".project_results pr WHERE p.id=pr.id AND pr.result=rd.id GROUP BY rd.datasource, p.title;


  		




  914
  914
  
    

  		




  915
  915
  
    
-- project charts


  		




  916
  916
  
    
-- SELECT p.id, r.year, count( distinct r.id) INTO "shadow".chart_project_year FROM "shadow".result r, "shadow".result_projects rp, "shadow".project p WHERE r.id=rp.id AND p.id=rp.project AND r.year>=p.start_year AND r.year<=p.end_year GROUP BY p.id, r.year;


  		












Also available in:  Unified diff