Sql-server – Insert Into with Select works in once instance, but not the other. Subquery error or incorrect data

insertselectsql server 2014subquery

I am trying to write a script that will combine specific survey results based on the demographs used in the survey. I have been able to write a script that adds demographs together to create a new demograph e.g. M18+ and F18+ = A18+ which works perfectly however when trying to create a script that will create a demograph range e.g. A18-24, i am encountering some issues i cannot resolve.

My first script that adds two demographs together is as follows:

create table #SurveyTemp
(
Com nchar(6),
Survey nvarchar(50),
Demo nchar(50),
Wk int,
Time int,
Aud decimal(18,8)
);

insert into #SurveyTemp (Com, Survey, Demo, Wk, Time, Aud)
select   Com, Survey, 'A18+', Wk, Time, sum(Aud)
from     table_survey
where    survey = 'LO2017'
and      demograph like 'F18+'
or       surveyid = 'LO2017'
and      demograph like 'M18+'
group by Com, survey, Wk, Time:

insert into table_survey
select temp.Com, temp.Survey, temp.Demo, temp.Wk, temp.Time, temp.Aud
from   #SurveyTemp temp

drop table #SurveyTemp


This script works fine. It adds two demographs together and inserts new rows of data, creating a sum of the audience for each Com, Survey, Wk, and then Time. See the sample data set below where the addition of M18+ and F18+ has created a new record called A18+

(Disclaimer: the live database has MANY rows for each demograph)

com | surveyid | demo | wk | time | audience
-------------------------
1 | LO2017    | A18+ | 1  | 300  |   4.7
1 | LO2017    | F18+ | 1  | 300  |   1.9
1 | LO2017    | M18+ | 1  | 300  |   2.8
1 | LO2017    | A25+ | 1  | 300  |   2.3
1 | LO2017    | A18+ | 2  | 100  |   3.7
1 | LO2017    | F18+ | 2  | 100  |   1.9
1 | LO2017    | M18+ | 2  | 100  |   2.8
1 | LO2017    | A25+ | 2  | 100  |   4.3


What I need to write now is a script that creates a range. Notice how in the table above there is a A18+ and a A25+. Please bear in mind that in my live table there will be MULTIPLE rows for all demographs.

In this example, I will be using the above table. So, I need to create a similar script that will sum the audience for two demographs and then subtract one from the other to create a range. For example, subtract the A25+ data from A18+ to create a range called A1824. I hope that makes sense.

In the script below, I am trying to create a range called 'A1824' but I am either getting a "subquery returned more than 1 row" error OR it is inserting the wrong data into the temp table where the sum in AudOne and AudTwo is the SAME for every single Wk and Time.

create table #SurveyTemp
(
Com nchar(6),
Survey nvarchar(50),
Demo nchar(50),
Wk int,
Time int,
Aud decimal(18,8),
AudOne decimal(18,8),
AudTwo decimal(18,8)
);

insert into #SurveyTemp (Com, Survey, Demo, Wk, TimeBlock, Aud, AudOne, AudTwo)
(select   Com, Survey, 'A1824', Wk, Time, 0.0,
(select sum(aud) from table_survey where demo = 'A18+'),
(select sum(aud) from table_survey where demo = 'A25+')
from     table_survey
where    surveyid = 'LO2017'
group by Com, survey, Wk, Time,
)

update #SurveyTemp
set Aud = AudOne - AudTwo;

insert into table_survey
select temp.Com, temp.survey, temp.demo, temp.Wk, temp.Time, temp.aud
from   #SurveyTemp temp

drop table #SurveyTemp


It is this second script that i need to get working and have spent hours trying to resolve without much luck.

Edit:
My expected results would be something like the following:

com | surveyid | demo  | wk | time | audience
-------------------------
1  | LO2017   | A1824 | 1  | 300  |   2.4


But there would be multiple rows where there are multiple 'wk' and 'time' entries. E.g.:

com | surveyid | demo  | wk | time | audience
-------------------------
1  | LO2017   | A1824 | 1  | 100  |   2.4
1  | LO2017   | A1824 | 1  | 200  |   3.7
1  | LO2017   | A1824 | 2  | 100  |   2.1
1  | LO2017   | A1824 | 2  | 200  |   6.2


I'll start after you has generated #SurveyTemp table.

I'm not sure to understand your expected result, take a look at your table ordered by demo:

SELECT   *
FROM     @table_survey
WHERE    demo IN ('A18+','A25+')
ORDER BY com, survey_id, demo, wk;

com | survey_id | demo | wk | time | audience |
--: | :-------- | :--- | -: | ---: | :------- |
1 | LO2017    | A18+ |  1 |  300 | 4.7      |
1 | LO2017    | A18+ |  2 |  100 | 3.7      |
1 | LO2017    | A25+ |  1 |  300 | 2.3      |
1 | LO2017    | A25+ |  2 |  100 | 4.3      |


There are 2 records for each demo. If you want to add new record as a result of some operation between these two records, you should decide if you want to group by wk and time (that I think it's the correct way), or not.

insert into #SurveyTemp (Com, Survey, Demo, Wk, TimeBlock, Aud, AudOne, AudTwo)
(select   Com, Survey, 'A1824', Wk, Time, 0.0,
(select sum(aud) from table_survey where demo = 'A18+'),
(select sum(aud) from table_survey where demo = 'A25+')
from     table_survey
where    surveyid = 'LO2017'
group by Com, survey, Wk, Time,
)


You're grouping by Com, Survey, Wk, Time, but you're calculating overall audience of all demo records, (without considering wk and time):

(select sum(aud) from table_survey where demo = 'A18+')
(select sum(aud) from table_survey where demo = 'A25+')


IMHO, at least wk should be added to the new records, and you should decide what to do with time.

My proposed solution: GROUP BY WK AND TIME

SELECT   Com, survey_id, 'A1824', wk, time,
(SELECT sum(audience) FROM @table_survey
WHERE demo = 'A18+' AND wk = t1.wk AND time = t1.time)
-
(SELECT sum(audience) FROM @table_survey
WHERE demo = 'A25+' AND wk = t1.wk AND time = t1.time) AS audience
FROM     @table_survey t1
WHERE    survey_id = 'LO2017'
GROUP BY Com, survey_id, Wk, time;


NOTE: I've added wk and time to calculate sum(audience) for every demo.

Com | survey_id | (No column name) | wk | time | audience
--: | :-------- | :--------------- | -: | ---: | :-------
1 | LO2017    | A1824            |  1 |  300 | 2.4
1 | LO2017    | A1824            |  2 |  100 | -0.6


As you can see, first record match your expected result.

Without group by wk and time

SELECT   Com, survey_id, 'A1824', 0 AS wk,
avg(time) as time,
(SELECT sum(audience) FROM @table_survey WHERE demo = 'A18+')
-
(SELECT sum(audience) FROM @table_survey WHERE demo = 'A25+') AS audience
FROM     @table_survey t1
WHERE    survey_id = 'LO2017'
GROUP BY Com, survey_id;


This is the result:

Com | survey_id | (No column name) | wk | time | audience
--: | :-------- | :--------------- | -: | ---: | :-------
1 | LO2017    | A1824            |  0 |  200 | 1.8


NOTE: You don't need to INSERT & UPDATE, it can be done on a single INSERT operation.

Check it: dbfiddle here