Project

General

Profile

« Previous | Next » 

Revision 6179

Added by Aaron Marcuse-Kubitza over 11 years ago

schemas/vegbien.sql: Renamed reference -> source to make this table more broadly applicable, and because this now stores the datasource metadata

View differences:

verify.specimens.sql
1 1 
SELECT '# specimen replicates' AS ___;
2 2 
SELECT count(*) AS count
3 3 
FROM specimenreplicate
4 
WHERE reference_id =
5 
    (SELECT reference_id FROM reference WHERE shortname = :datasource)
4 
WHERE source_id =
5 
    (SELECT source_id FROM source WHERE shortname = :datasource)
6 6 
;
7 7 

  		




  8
  8
  
    
SELECT '# families' AS ___;


  		




  9
  9
  
    
SELECT count(DISTINCT family) AS count


  		




  10
  10
  
    
FROM taxonlabel


  		




  11
  11
  
    
JOIN taxonverbatim USING (taxonlabel_id)


  		




  12
  
  
    
WHERE reference_id =


  		




  13
  
  
    
    (SELECT reference_id FROM reference WHERE shortname = :datasource)


  		




  
  12
  
    
WHERE source_id =


  		




  
  13
  
    
    (SELECT source_id FROM source WHERE shortname = :datasource)


  		




  14
  14
  
    
;


  		




  15
  15
  
    

  		




  16
  16
  
    
SELECT '# genera' AS ___;


  		




  17
  17
  
    
SELECT count(DISTINCT genus) AS count


  		




  18
  18
  
    
FROM taxonlabel


  		




  19
  19
  
    
JOIN taxonverbatim USING (taxonlabel_id)


  		




  20
  
  
    
WHERE reference_id =


  		




  21
  
  
    
    (SELECT reference_id FROM reference WHERE shortname = :datasource)


  		




  
  20
  
    
WHERE source_id =


  		




  
  21
  
    
    (SELECT source_id FROM source WHERE shortname = :datasource)


  		




  22
  22
  
    
;


  		




  23
  23
  
    

  		




  24
  24
  
    
SELECT '# species' AS ___;


  		




  25
  25
  
    
SELECT count(DISTINCT specific_epithet) AS count


  		




  26
  26
  
    
FROM taxonlabel


  		




  27
  27
  
    
JOIN taxonverbatim USING (taxonlabel_id)


  		




  28
  
  
    
WHERE reference_id =


  		




  29
  
  
    
    (SELECT reference_id FROM reference WHERE shortname = :datasource)


  		




  
  28
  
    
WHERE source_id =


  		




  
  29
  
    
    (SELECT source_id FROM source WHERE shortname = :datasource)


  		




  30
  30
  
    
;


  		




  31
  31
  
    

  		




  32
  32
  
    
SELECT '# binomials' AS ___;


  		




  33
  33
  
    
SELECT count(DISTINCT taxonverbatim_id) AS count


  		




  34
  34
  
    
FROM taxonlabel


  		




  35
  35
  
    
JOIN taxonverbatim USING (taxonlabel_id)


  		




  36
  
  
    
WHERE reference_id =


  		




  37
  
  
    
    (SELECT reference_id FROM reference WHERE shortname = :datasource)


  		




  
  36
  
    
WHERE source_id =


  		




  
  37
  
    
    (SELECT source_id FROM source WHERE shortname = :datasource)


  		




  38
  38
  
    
;


  		




  39
  39
  
    

  		




  40
  40
  
    
SELECT 'collection codes' AS ___;


  		




  41
  41
  
    
SELECT DISTINCT collectioncode_dwc


  		




  42
  42
  
    
FROM specimenreplicate


  		




  43
  
  
    
WHERE reference_id =


  		




  44
  
  
    
    (SELECT reference_id FROM reference WHERE shortname = :datasource)


  		




  
  43
  
    
WHERE source_id =


  		




  
  44
  
    
    (SELECT source_id FROM source WHERE shortname = :datasource)


  		




  45
  45
  
    
;


  		




  46
  46
  
    

  		




  47
  47
  
    
SELECT '# catalog numbers' AS ___;


  		




  48
  48
  
    
SELECT count(DISTINCT catalognumber_dwc) AS count


  		




  49
  49
  
    
FROM specimenreplicate


  		




  50
  
  
    
WHERE reference_id =


  		




  51
  
  
    
    (SELECT reference_id FROM reference WHERE shortname = :datasource)


  		




  
  50
  
    
WHERE source_id =


  		




  
  51
  
    
    (SELECT source_id FROM source WHERE shortname = :datasource)


  		




  52
  52
  
    
;


  		




  53
  53
  
    

  		




  54
  54
  
    
SELECT 'duplicate catalog numbers' AS ___;


  		




  55
  55
  
    
SELECT catalognumber_dwc, count(*) AS count


  		




  56
  56
  
    
FROM specimenreplicate


  		




  57
  
  
    
WHERE reference_id =


  		




  58
  
  
    
    (SELECT reference_id FROM reference WHERE shortname = :datasource)


  		




  
  57
  
    
WHERE source_id =


  		




  
  58
  
    
    (SELECT source_id FROM source WHERE shortname = :datasource)


  		




  59
  59
  
    
GROUP BY catalognumber_dwc


  		




  60
  60
  
    
HAVING count(*) > 1


  		




  61
  61
  
    
ORDER BY catalognumber_dwc


  		




  ......




  64
  64
  
    
SELECT '# counties' AS ___;


  		




  65
  65
  
    
SELECT count(DISTINCT county) AS count


  		




  66
  66
  
    
FROM place


  		




  67
  
  
    
WHERE reference_id =


  		




  68
  
  
    
    (SELECT reference_id FROM reference WHERE shortname = :datasource)


  		




  
  67
  
    
WHERE source_id =


  		




  
  68
  
    
    (SELECT source_id FROM source WHERE shortname = :datasource)


  		




  69
  69
  
    
;


  		












Also available in:  Unified diff