July 6, 2016 at 3:22 pm
Hello,
Is it possible to use a cursor to INSERT multiple columns and rows using GROUP BY?
I am trying to create a temp table that will calculate data from another table to sum up the results. Here is my sample data. However, I get the error message, "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
Is there a better way to do this?
-- Create Sample Accounts Table
CREATE TABLE #Accts(Acct int)
INSERT INTO #Accts VALUES (1111)
INSERT INTO #Accts VALUES (2222)
INSERT INTO #Accts VALUES (3333)
INSERT INTO #Accts VALUES (4444)
INSERT INTO #Accts VALUES (5555)
INSERT INTO #Accts VALUES (6666)
INSERT INTO #Accts VALUES (7777)
INSERT INTO #Accts VALUES (8888)
INSERT INTO #Accts VALUES (9999)
-- Create Sample Data Table
CREATE TABLE #getData(Period int, Acct int, Period_Sales money, TranType varchar(6))
INSERT INTO #getData VALUES (3,1111,'50.00','Sale')
INSERT INTO #getData VALUES (3,2222,'15.00','Sale')
INSERT INTO #getData VALUES (3,2222,'50.00','Sale')
INSERT INTO #getData VALUES (4,3333,'10.00','Sale')
INSERT INTO #getData VALUES (4,9999,'25.00','Sale')
INSERT INTO #getData VALUES (4,9999,'35.00','Sale')
INSERT INTO #getData VALUES (5,9999,'35.00','Sale')
INSERT INTO #getData VALUES (6,4444,'65.00','Sale')
INSERT INTO #getData VALUES (6,9999,'105.00','Sale')
INSERT INTO #getData VALUES (7,9999,'75.00','Sale')
INSERT INTO #getData VALUES (6,9999,'75.00','Return')
INSERT INTO #getData VALUES (7,9999,'15.00','Return')
DECLARE
@Acctint,
@PD_Salesmoney,
@PD_Retmoney
DECLARE db_Cursor CURSOR FOR SELECT Acct FROM #Accts
OPEN db_Cursor
FETCH NEXT FROM db_Cursor
INTO @Acct
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PD_Sales= (SELECT Acct, Period, SUM(Period_sales) FROM #GetData WHERE Acct=@Acct AND TranType='Sale' GROUP BY Acct, Period)
SET @PD_Ret= (SELECT Acct, Period, SUM(Period_sales) FROM #GetData WHERE Acct=@Acct AND TranType='Return' GROUP BY Acct, Period)
CREATE TABLE #PD_Data (Acct int, Period int, PD_Sales money, PD_Ret money, PD_Tot_Sales money)
INSERT INTO #PD_Data(Acct, Period, PD_Sales, PD_Ret, PD_Tot_Sales)
SELECT Acct, Period, @PD_Sales, @PD_Ret, ((@PD_Sales) - (@PD_Ret)) as PD_Tot_Sales
FETCH NEXT FROM db_Cursor INTO @Acct
END
CLOSE db_Cursor
DEALLOCATE db_Cursor
July 6, 2016 at 3:34 pm
Can't you do a simple
INSERT INTO DestinationTable(Col1, Col2, Col3...)
SELECT GrpBy1, GrpBy2, SUM(NumericField)
FROM SrcTable
GROUP BY GrpBy1, GrpBy2;
July 6, 2016 at 3:37 pm
rjjh78 (7/6/2016)
Hello,Is it possible to use a cursor to INSERT multiple columns and rows using GROUP BY?
I am trying to create a temp table that will calculate data from another table to sum up the results. Here is my sample data. However, I get the error message, "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
Is there a better way to do this?
-- Create Sample Accounts Table
CREATE TABLE #Accts(Acct int)
INSERT INTO #Accts VALUES (1111)
INSERT INTO #Accts VALUES (2222)
INSERT INTO #Accts VALUES (3333)
INSERT INTO #Accts VALUES (4444)
INSERT INTO #Accts VALUES (5555)
INSERT INTO #Accts VALUES (6666)
INSERT INTO #Accts VALUES (7777)
INSERT INTO #Accts VALUES (8888)
INSERT INTO #Accts VALUES (9999)
-- Create Sample Data Table
CREATE TABLE #getData(Period int, Acct int, Period_Sales money, TranType varchar(6))
INSERT INTO #getData VALUES (3,1111,'50.00','Sale')
INSERT INTO #getData VALUES (3,2222,'15.00','Sale')
INSERT INTO #getData VALUES (3,2222,'50.00','Sale')
INSERT INTO #getData VALUES (4,3333,'10.00','Sale')
INSERT INTO #getData VALUES (4,9999,'25.00','Sale')
INSERT INTO #getData VALUES (4,9999,'35.00','Sale')
INSERT INTO #getData VALUES (5,9999,'35.00','Sale')
INSERT INTO #getData VALUES (6,4444,'65.00','Sale')
INSERT INTO #getData VALUES (6,9999,'105.00','Sale')
INSERT INTO #getData VALUES (7,9999,'75.00','Sale')
INSERT INTO #getData VALUES (6,9999,'75.00','Return')
INSERT INTO #getData VALUES (7,9999,'15.00','Return')
DECLARE
@Acctint,
@PD_Salesmoney,
@PD_Retmoney
DECLARE db_Cursor CURSOR FOR SELECT Acct FROM #Accts
OPEN db_Cursor
FETCH NEXT FROM db_Cursor
INTO @Acct
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PD_Sales= (SELECT Acct, Period, SUM(Period_sales) FROM #GetData WHERE Acct=@Acct AND TranType='Sale' GROUP BY Acct, Period)
SET @PD_Ret= (SELECT Acct, Period, SUM(Period_sales) FROM #GetData WHERE Acct=@Acct AND TranType='Return' GROUP BY Acct, Period)
CREATE TABLE #PD_Data (Acct int, Period int, PD_Sales money, PD_Ret money, PD_Tot_Sales money)
INSERT INTO #PD_Data(Acct, Period, PD_Sales, PD_Ret, PD_Tot_Sales)
SELECT Acct, Period, @PD_Sales, @PD_Ret, ((@PD_Sales) - (@PD_Ret)) as PD_Tot_Sales
FETCH NEXT FROM db_Cursor INTO @Acct
END
CLOSE db_Cursor
DEALLOCATE db_Cursor
You are getting that error because of this
SET @PD_Sales= (SELECT Acct, Period, SUM(Period_sales) FROM #GetData WHERE Acct=@Acct AND TranType='Sale' GROUP BY Acct, Period)
You are trying to set the value for a scalar value with a query that has three columns. How does sql know which column you want? It doesn't, so it throws an exception.
Lucky for you this happened. We can fix this query by throwing away that cursor and doing this as a set based insert instead.
I used a cte here to avoid having to calculate the SUM multiple times. This should replace the ENTIRE cursor logic you posted. No need for any cursors or variable at all.
with GroupedData as
(
SELECT Acct
, Period
, SUM(case when TranType = 'Sale' then Period_sales else 0 end) as PD_Sales
, SUM(case when TranType = 'Return' then Period_sales else 0 end) as PD_Ret
from #GetData
GROUP BY Acct
, Period
)
INSERT INTO #PD_Data(Acct, Period, PD_Sales, PD_Ret, PD_Tot_Sales)
select Acct
, Period
, PD_Sales
, PD_Ret
, PD_Sales - PD_Ret
from GroupedData
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 7, 2016 at 7:47 am
this is awesome, thank you!!!
July 7, 2016 at 7:57 am
rjjh78 (7/7/2016)
this is awesome, thank you!!!
I am glad it worked for you but more important...do you understand it?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 7, 2016 at 8:07 am
Yes, I do understand what is happening, I was not aware you could do this. I know there is also, with CTE. What is the "with" function all about? I will need to study this.
July 7, 2016 at 8:29 am
rjjh78 (7/7/2016)
Yes, I do understand what is happening, I was not aware you could do this. I know there is also, with CTE. What is the "with" function all about? I will need to study this.
That is a cte (common table expression). You can read about them here.
https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
https://msdn.microsoft.com/en-us/library/ms175972.aspx
https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/%5B/url%5D
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply