CODEWITHSUNDEEP
sqlsql-server
MB9
MB9

Reputation: 73

Assign Aggregate Function Result to A Variable SQL

Does SQL allow assigning the return of an aggregate function to another variable of the same type? I’m trying to take the Count(ID) of a table and assign the returned result to a temp table and my query continues to error with an ‘aggregate function error’ so I’m wondering if the return value of COUNT(ID) is able to be assigned to a variable?

SELECT Count(ID) From SomeTable

DECLARE @ttl INT 

SET @ttl = Count(ID)

I’d like to take this returned number, let’s say 500, and then assign it to an INT variable in a temp table

DECLARE @cnt INT = 0;                                   
DECLARE @startYear DATETIME = '2011-01-01 00:00:00.000' 
DECLARE @endYear DATETIME   = '2011-12-31 23:59:59.999' 
DECLARE @ttl INT                                

SELECT DATEPART(year, @startYear) AS [Year of Totals], COUNT(ID) AS [Total For Year], GETDATE() AS [DATE/TIME of RUN]  
FROM SomeTable
ORDER BY [Year of Totals]

    SET @cnt = @cnt + 1;                           
    SET @startYear = DATEADD(year, 1, @startYear);  
    SET @endYear = DATEADD(year, 1, @endYear);     
    SET @ttl = COUNT(ID);   --> Problem Points to this as Invalid column 'ID'

INSERT INTO TempYearlyResults (Year, Total_YEAR, Date_Time, Test_Data) VALUES (DATEADD(YEAR,-1, @startYear), @ttl, GETDATE(), 'Is this working')

Editing original post with more details - Pic For Reference:enter image description here

--Pseudocode BEGIN

-- Variable Declarations

-- Initialize counter to zero

--> EDIT this for the start year

--> EDIT this for that end of start year

-- Initialize for Total Counts for Year

-- Create A Temp Table to Drop Loop Data into

--WHILE the counter is less than the number of years being requested, run the following WHILE LOOP WHILE @cnt < 3 --> EDIT this for the complete number of years being requested BEGIN

-- SELECT the Year, Total Count for said Year, and Date/Time this run is being executed

-- Increment the counter to display the next year in the loop

-- Increment the Start Year to the next year

-- Increment the end of the start year

-- Set @ttl Variable equal to the Total For Year of each year

-- Populate the Temp Table with the results

-- Show the collected results in the Temp Table

-- Drop Table after populating to allow for creation next time

-- Pseudocode END

Upvotes: 0

Views: 1440

Answers (1)

ListenSoftware Louise Ai Agent
ListenSoftware Louise Ai Agent

Reputation: 4253

try assigning the variable in the sql. @startYear and @EndYear should be used as Where filters and not part of the groupings

DECLARE @ttl INT 
SELECT @ttl=Count(distinct ID) From SomeTable

select @ttl

Try partitioning ids by Year then counting them

  SELECT distinct DATEPART(year, YourDate) AS [Year of Totals], 
     COUNT(ID) over(partition by DATEPART(year, YourDate) order by DATEPART(year, Date))  AS [Total For Year], 
     GETDATE() AS [DATE/TIME of RUN]  
FROM SomeTable

Try a Cursor, increment a counter variable and store the results in a temp table after each year changes.

declare c1  cursor for
    select ID, Datepart(year, theDate) Year from SomeTable ORDER BY Datepart(year, theDate);
open c1

FETCH NEXT FROM c1 INTO @ID,@Year;
declare @Counter as int=0
declare @PrevYear as int=0
WHILE @@FETCH_STATUS = 0  
    BEGIN
       set @Counter=@Counter+1
       if @Year<>@PrevYear then
            begin
                 print @Counter
                 set @Counter=0
            end
       set @PrevYear=@Year
       FETCH NEXT FROM c1 INTO @ID,@Year;
    END

close c1
deallocate c1

Upvotes: 1

Related Questions