 ),
 specimens_data as 
 (
-select 'BIO' as "Branch", 
+select case 
+         when (collection_ref = 3 or collection_path like '/3/%') then
+           'PALEO'
+         when (collection_ref = 231 or collection_path like '/231/%') then
+           'MIN'
+         else
+           'BIO'
+       end as "Branch", 
        case
          when strpos(taxon_path, '/91/') != 0 and 
 	     (collection_ref = 1 or collection_path like '/1/%') then

              strpos(taxon_path, '/65/') != 0 or 
              strpos(taxon_path, '/177320/') != 0 or
              strpos(taxon_path, '/276/') != 0 or
              (strpos(taxon_path, '/2/') != 0 and 
	       strpos(taxon_path, '/61/') = 0 and 
	       strpos(taxon_path, '/353/') = 0 and 
	       strpos(taxon_path, '/184176/') = 0 and 
	       strpos(taxon_path, '/177325/') = 0 and
	       strpos(taxon_path, '/64/') = 0 and
	       strpos(taxon_path, '/65/') = 0 and 
	       strpos(taxon_path, '/177320/') = 0 and
	       strpos(taxon_path, '/276/') = 0
	      ) or
	      strpos(taxon_path, '/109920/') != 0 or
	      strpos(taxon_path, '/30/') != 0 or
	      strpos(taxon_path, '/72/') != 0 or

	      strpos(taxon_path, '/19/') != 0 or
	      strpos(taxon_path, '/81/') != 0 or
	      strpos(taxon_path, '/83/') != 0 or
	      strpos(taxon_path, '/90/') != 0 or
              (strpos(taxon_path, '/2/') != 0 and 
	       strpos(taxon_path, '/61/') = 0 and 
	       strpos(taxon_path, '/353/') = 0 and 
	       strpos(taxon_path, '/184176/') = 0 and 
	       strpos(taxon_path, '/177325/') = 0 and
	       strpos(taxon_path, '/64/') = 0 and
	       strpos(taxon_path, '/65/') = 0 and 
	       strpos(taxon_path, '/177320/') = 0 and
	       strpos(taxon_path, '/276/') = 0
	      ) 
	     ) and 
	     (collection_ref = 2 or collection_path like '/2/%') 
          then
          'IZ'
        when (
               strpos(taxon_path, '/4/') != 0 or
               strpos(taxon_path, '/12/') != 0 or 
               strpos(taxon_path, '/154425/') != 0 or 
               strpos(taxon_path, '/10/') != 0 or
               strpos(taxon_path, '/406/') != 0 or
               strpos(taxon_path, '/408/') != 0 or
               strpos(taxon_path, '/50/') != 0 or
               strpos(taxon_path, '/423/') != 0 or
               strpos(taxon_path, '/413/') != 0 or
               strpos(taxon_path, '/11/') != 0 or
	       strpos(taxon_path, '/47/') != 0 or 
	       strpos(taxon_path, '/106418/') != 0 or
               (
                 strpos(taxon_path, '/2/') != 0 and 
	         strpos(taxon_path, '/4/') = 0 and 
	         strpos(taxon_path, '/12/') = 0 and 
	         strpos(taxon_path, '/10/') = 0 and 
	         strpos(taxon_path, '/406/') = 0 and
	         strpos(taxon_path, '/408/') = 0 and
	         strpos(taxon_path, '/50/') = 0 and 
	         strpos(taxon_path, '/423/') = 0 and
	         strpos(taxon_path, '/413/') = 0 and 
	         strpos(taxon_path, '/11/') = 0 and
	         strpos(taxon_path, '/47/') = 0
               )
             ) and 
	     (collection_ref = 4 or collection_path like '/4/%') then
	  'Entomology'
        when strpos(chrono_path, '/11/') != 0 and
             (collection_ref = 3 or collection_path like '/3/%') then
          'Cenozoic'           
        when strpos(chrono_path, '/26/') != 0 and
             (collection_ref = 3 or collection_path like '/3/%') then
          'Mesozoic'           
        when strpos(chrono_path, '/5/') != 0 and
             (collection_ref = 3 or collection_path like '/3/%') then
          'Paleozoic'           
        when (collection_ref = 200 or collection_path like '/200/%') then
          'Meteorites'
        when (collection_ref = 276) then
          'Gems'
        else
          'Other'
      end in ('VZ', 'IZ', 'Entomology', 'Cenozoic', 'Mesozoic', 'Paleozoic', 'n/a', 'Meteorites', 'Gems', 'Other')
group by "Branch", 
       "Department",
       "Group",
