Nested loops in SQL Server

I have a table which contains among other fields: sites, collection years, treatments, samples, species, and counts of seedlings.

I want to be able to loop through the table and select the 5 most abundant species grouped by:  sites, collection years, treatments,species.

In the t-sql script that I have created, I have been unable to get the looping to work correctly. The outer loop ( cycling thru sites) is not being executed.

My intention is for the inner loop to cycle through treatments, the middle loop cycles through years and the outer loop cycles through sites.

I have attached a zip of the sql script and the results of the script as a csv file.
dblankmanAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
RimvisConnect With a Mentor Commented:
Check if this works for you:

DECLARE @seedlingtable TABLE
  (
     siteid          VARCHAR(50),
     sitenumber      INT,
     Treatment       VARCHAR(50),
     treatmentnumber INT,
     CollectionYear  INT,
     samplekey       VARCHAR(50),
     Species         VARCHAR(50),
     seedlingCount   INT
  )

INSERT INTO @seedlingtable
            (siteid,
             sitenumber,
             Treatment,
             treatmentnumber,
             CollectionYear,
             samplekey,
             Species,
             seedlingCount)
SELECT db_owner.Seedbank.site_id,
       CASE
         WHEN site_id = 'B' THEN 5
         WHEN site_id = 'L' THEN 2
         WHEN site_id = 'M' THEN 3
         WHEN site_id = 'Y' THEN 4
       END                                      AS sitenumber,
       db_owner.Seedbank.Treatment,
       CASE
         WHEN treatment = 'I' THEN 1
         WHEN treatment = 'D' THEN 2
         WHEN treatment = 'C' THEN 3
       END                                      AS TreatmentNumber,
       db_owner.Seedbank.Collection_Year,
       db_owner.Seedbank.SampleKey,
       db_owner.Glowa_Species_List.Species_Name AS species,
       Sum(db_owner.Seedbank.Seedling_Count)    AS NumberSeedlings
FROM   db_owner.Seedbank
       INNER JOIN db_owner.Glowa_Species_List
               ON db_owner.Seedbank.Species = db_owner.Glowa_Species_List.Species_Code
WHERE  ( db_owner.Seedbank.Seedling_Count <> 999 )
       AND db_owner.Seedbank.Seedling_Count <> 0
GROUP  BY db_owner.Seedbank.site_id,
          db_owner.Seedbank.Treatment,
          db_owner.Seedbank.Collection_Year,
          db_owner.Seedbank.sampleKey,
          db_owner.Glowa_Species_List.Species_Name
          
;WITH seedling_total AS 
(
	select siteid,sitenumber,CollectionYear,treatment,treatmentnumber,species,sum(seedlingCount) as abundance, count(distinct samplekey) AS countSamples
	from @seedlingtable
	GROUP BY siteid,sitenumber,CollectionYear,treatment,treatmentnumber,Species
),
seedling_total_filtered AS 
(
	SELECT siteid, sitenumber, Treatment, treatmentnumber, CollectionYear, Species, abundance, countSamples, 
		ROW_NUMBER() OVER(PARTITION BY sitenumber,CollectionYear,treatment ORDER BY  abundance desc) AS RowNo
	FROM seedling_total
)
SELECT * FROM seedling_total_filtered WHERE RowNo <=5

Open in new window

0
 
RimvisCommented:
Hi dblankman,

I cannot see any files attached. Can you try again please?
BTW, you shouldn't us loops in SQL for such tasks. I'm sure you can achieve the same results with SELECT statements
0
 
dblankmanAuthor Commented:
zipped file of sql script and results
sqlAndcsvFiles.zip
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
RimvisCommented:
Could you also post your table schema?
0
 
dblankmanAuthor Commented:
CREATE TABLE [db_owner].[Seedbank] (
    [Sample_Unique_Key] NVARCHAR (255) NOT NULL,
    [Collection_Year]   SMALLINT       NULL,
    [Site_ID]           NVARCHAR (255) NULL,
    [Germination_Year]  SMALLINT       NULL,
    [Treatment]         NVARCHAR (255) NULL,
    [Plot_ID]           NVARCHAR (255) NULL,
    [Block_ID]          NVARCHAR (255) NULL,
    [Habitat_ID]        NVARCHAR (255) NULL,
    [Sample_Number]     SMALLINT       NULL,
    [Block_net_house]   SMALLINT       NULL,
    [Run_Number]        SMALLINT       NULL,
    [Species]           NVARCHAR (255) NULL,
    [Seedling_Count]    SMALLINT       NULL,
    [table_number]      INT            NULL,
    [SampleKey]         NVARCHAR (50)  NULL
);
0
 
RimvisCommented:
Thanks,
what about db_owner.Glowa_Species_List?
0
 
dblankmanAuthor Commented:
[db_owner].[Glowa_Species_List] (
    [Species_Code]             NVARCHAR (255) NULL,
    [Family]                   NVARCHAR (255) NULL,
    [Genus]                    NVARCHAR (255) NULL,
    [Species_Name]             NVARCHAR (255) NULL,
    [Synonym_1]                NVARCHAR (255) NULL,
    [Synonym_2]                NVARCHAR (255) NULL,
    [Synonym_3]                NVARCHAR (255) NULL,
    [Plant_Functional_Group]   NVARCHAR (255) NULL,
    [Marcelos_Functional_Type] NVARCHAR (255) NULL,
    [Growth_Form]              NVARCHAR (255) NULL,
    [Systematic_Number]        FLOAT (53)     NULL,
    [Fragman_Hebrew_Name]      NVARCHAR (255) NULL,
    [Reshut_Hebrew_Name]       NVARCHAR (255) NULL,
    [Notes]                    NVARCHAR (255) NULL
);
0
 
SharathData EngineerCommented:
Post the sample data from Seedbank and Glowa_Species_List tables with the expected result. I think the looping can be avoided.
0
 
dblankmanAuthor Commented:
0
 
dblankmanAuthor Commented:
uploaded sample data  (see previous comment)
0
 
SharathData EngineerCommented:
And what is the expected output?
0
 
dblankmanAuthor Commented:
see file in comment:  zipped file of sql script and result
0
 
dblankmanAuthor Commented:
sample results, but with all 4 sites:

siteid      sitenumber      Treatment      treatmentnumber      CollectionYear      Species      abundance      countSamples
L      2      I      1      2001      Filago contracta      631      64
L      2      I      1      2001      Trisetaria macrochaeta      307      55
L      2      I      1      2001      Crithopsis delileana      300      59
L      2      I      1      2001      Valantia hispida      111      12
L      2      I      1      2001      Rostraria cristata      85      24
L      2      D      2      2001      Trisetaria macrochaeta      739      76
L      2      D      2      2001      Filago contracta      448      67
L      2      D      2      2001      Crithopsis delileana      231      49
L      2      D      2      2001      Poa bulbosa      227      9
L      2      D      2      2001      Sedum rubens      162      17
L      2      C      3      2001      Trisetaria macrochaeta      684      66
L      2      C      3      2001      Filago contracta      606      58
L      2      C      3      2001      Crithopsis delileana      228      53
L      2      C      3      2001      Sarcopoterium spinosum      91      28
L      2      C      3      2001      Anagallis arvensis      83      37
L      2      I      1      2002      Crithopsis delileana      409      31
L      2      I      1      2002      Trisetaria macrochaeta      211      33
L      2      I      1      2002      Sarcopoterium spinosum      101      25
L      2      I      1      2002      Filago contracta      76      22
L      2      I      1      2002      Allium spp.      63      16
L      2      D      2      2002      Trisetaria macrochaeta      392      41
L      2      D      2      2002      Crithopsis delileana      275      29
L      2      D      2      2002      Sarcopoterium spinosum      82      23
L      2      D      2      2002      Catapodium rigidum      81      13
L      2      D      2      2002      Filago contracta      81      22
L      2      C      3      2002      Trisetaria macrochaeta      393      42
L      2      C      3      2002      Crithopsis delileana      137      30
0
 
dblankmanAuthor Commented:
Thanks. I need to learn about the ;with construction
0
 
RimvisCommented:
It's called Common Table Expressions (CTE)

You might start here:
https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.