Help please. Complicated sql with multiple sub queries not working

Question

1.00/5 (1 vote)

See more:

Hi All,

I have the following long query and I cant seem to get it working the way I would like! The error I get is:

Msg 512, Level 16, State 1, Line 31
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Can anyone tell me what/how I need to change this to get it working please?

The output should look something like the following in SSRS. Then I can have an expression to calculate events and points etc...

Boarding House | + Events | + Points | - Events | - Points
House 1        | 10       | 20       | 10       | 10
House 2        | 5        | 10       | 5        | 5
etc...

I appreciate its quite complex, but any help/guidance would great!

Thanx in advance.

SQL

SELECT	PMP.txtBoardingHouse,

(SELECT	COUNT(RMRTC.intValue) AS exp1
FROM	TblPupilManagementPupils AS PMP 
	INNER JOIN TblRewardsManagerRewards AS RMR ON PMP.txtSchoolID = RMR.txtSchoolID 
	INNER JOIN TblRewardsManagerRewardTypes AS RMRT 
	INNER JOIN TblRewardsManagerRewardTypesCategories AS RMRTC 
                ON RMRT.TblRewardsManagerRewardTypesID = RMRTC.intType 
        	ON RMR.intCategory = RMRTC.TblRewardsManagerRewardTypesCategoriesID 
            AND RMR.intType = RMRT.TblRewardsManagerRewardTypesID
	WHERE	(RMRT.intPositive = 1) AND (PMP.txtSchoolID = RMR.txtSchoolID) 
            AND (RMR.txtDate BETWEEN '2013-01-01' AND '2014-01-01') 
            AND (RMRT.TblRewardsManagerRewardTypesID BETWEEN 19 AND 24) 
            AND (RMRTC.TblRewardsManagerRewardTypesCategoriesID BETWEEN 63 and 97)
	GROUP BY PMP.txtBoardingHouse) AS '+ Events',
 
(SELECT	SUM(RMRTC.intValue) AS exp2
FROM	TblPupilManagementPupils AS PMP 
        INNER JOIN TblRewardsManagerRewards AS RMR ON PMP.txtSchoolID = RMR.txtSchoolID 
	INNER JOIN TblRewardsManagerRewardTypes AS RMRT 
	INNER JOIN TblRewardsManagerRewardTypesCategories AS RMRTC 
                ON RMRT.TblRewardsManagerRewardTypesID = RMRTC.intType 
		ON RMR.intCategory = RMRTC.TblRewardsManagerRewardTypesCategoriesID 
            AND RMR.intType = RMRT.TblRewardsManagerRewardTypesID
	WHERE	(RMRT.intPositive = 1) AND (PMP.txtSchoolID = RMR.txtSchoolID) 
            AND (RMR.txtDate BETWEEN '2013-01-01' AND '2014-01-01') 
            AND (RMRT.TblRewardsManagerRewardTypesID BETWEEN 19 AND 24) 
            AND (RMRTC.TblRewardsManagerRewardTypesCategoriesID BETWEEN 63 and 97)
	GROUP BY PMP.txtBoardingHouse) AS '+ Points', 

(SELECT	COUNT(RMRTC.intValue) AS exp3
FROM	TblPupilManagementPupils AS PMP 
			INNER JOIN TblRewardsManagerRewards AS RMR ON PMP.txtSchoolID = RMR.txtSchoolID 
			INNER JOIN TblRewardsManagerRewardTypes AS RMRT 
			INNER JOIN TblRewardsManagerRewardTypesCategories AS RMRTC ON RMRT.TblRewardsManagerRewardTypesID = RMRTC.intType 
						ON RMR.intCategory = RMRTC.TblRewardsManagerRewardTypesCategoriesID AND RMR.intType = RMRT.TblRewardsManagerRewardTypesID
	WHERE	(RMRT.intPositive = - 1) AND (PMP.txtSchoolID = RMR.txtSchoolID) AND (RMR.txtDate BETWEEN '2013-01-01' AND '2014-01-01') 
			AND (RMRT.TblRewardsManagerRewardTypesID BETWEEN 19 AND 24) AND (RMRTC.TblRewardsManagerRewardTypesCategoriesID BETWEEN 63 and 97)
	GROUP BY PMP.txtBoardingHouse) AS '- Events', 

	(SELECT	SUM(RMRTC.intValue) AS exp4
	FROM	TblPupilManagementPupils AS PMP 
			INNER JOIN TblRewardsManagerRewards AS RMR ON PMP.txtSchoolID = RMR.txtSchoolID 
			INNER JOIN TblRewardsManagerRewardTypes AS RMRT 
			INNER JOIN TblRewardsManagerRewardTypesCategories AS RMRTC ON RMRT.TblRewardsManagerRewardTypesID = RMRTC.intType
						ON RMR.intCategory = RMRTC.TblRewardsManagerRewardTypesCategoriesID AND RMR.intType = RMRT.TblRewardsManagerRewardTypesID
	WHERE	(RMRT.intPositive = - 1) AND (PMP.txtSchoolID = RMR.txtSchoolID) AND (RMR.txtDate BETWEEN '2013-01-01' AND '2014-01-01') 
			AND (RMRT.TblRewardsManagerRewardTypesID BETWEEN 19 AND 24) AND (RMRTC.TblRewardsManagerRewardTypesCategoriesID BETWEEN 63 and 97)
	GROUP BY PMP.txtBoardingHouse) AS '- Points'

FROM	TblRewardsManagerRewards AS RMR
		INNER JOIN TblPupilManagementPupils AS PMP ON RMR.txtSchoolID = PMP.txtSchoolID 
		INNER JOIN TblRewardsManagerRewardTypes AS RMRT ON RMR.intType = RMRT.TblRewardsManagerRewardTypesID 
		INNER JOIN TblRewardsManagerRewardTypesCategories AS RMRTC ON RMR.intCategory = RMRTC.TblRewardsManagerRewardTypesCategoriesID
WHERE	(PMP.intNCYear BETWEEN 9 and 13) AND (RMRT.TblRewardsManagerRewardTypesID BETWEEN 19 AND 24) AND (RMRTC.TblRewardsManagerRewardTypesCategoriesID BETWEEN 63 and 97) 
		AND (PMP.txtForm IS NOT NULL) AND (RMR.txtDate BETWEEN '2013-01-01' AND '2014-01-01')

GROUP BY PMP.txtBoardingHouse
ORDER BY PMP.txtBoardingHouse

Posted 5-May-14 20:51pm

kswire87

Updated 5-May-14 20:57pm

thatraja

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-05-05T23:26:00

I believe it's possible to achieve in much simplest way:

SQL

SELECT	PMP.txtBoardingHouse AS House, RMRT.intPositive AS Descr, COUNT(RMRTC.intValue) AS EventsCount, SUM(RMRTC.intValue) AS ReachedPoints
FROM	TblPupilManagementPupils AS PMP 
	INNER JOIN TblRewardsManagerRewards AS RMR ON PMP.txtSchoolID = RMR.txtSchoolID 
	INNER JOIN TblRewardsManagerRewardTypes AS RMRT 
	INNER JOIN TblRewardsManagerRewardTypesCategories AS RMRTC 
                ON RMRT.TblRewardsManagerRewardTypesID = RMRTC.intType 
        	ON RMR.intCategory = RMRTC.TblRewardsManagerRewardTypesCategoriesID 
            AND RMR.intType = RMRT.TblRewardsManagerRewardTypesID
WHERE RMR.txtDate BETWEEN '2013-01-01' AND '2014-01-01' AND RMRT.TblRewardsManagerRewardTypesID BETWEEN 19 AND 24
      AND RMRTC.TblRewardsManagerRewardTypesCategoriesID BETWEEN 63 and 97
GROUP BY PMP.txtBoardingHouse, RMRT.intPositive

Result should look like:

House    Descr  EventsCount    ReachedPoints
House 1   1      10             20
House 1  -1      10             10
House 2   1       5             10
House 2  -1       5              5

Now, you can 'convert' it to expected format:

SQL

SELECT House, SUM(CASE WHEN Descr =1 THEN EventCount ELSE NULL END) AS [+Events],
    SUM(CASE WHEN Descr =-1 THEN EventCount ELSE NULL END) AS [-Events],
    SUM(CASE WHEN Descr =1 THEN ReachedPoints ELSE NULL END) AS [+Points], 
    SUM(CASE WHEN Descr =-1 THEN ReachedPoints ELSE NULL END) AS [-Points]
FROM (
--above query
) AS T
GROUP BY House

Note: Do not use the same query as a subquery to produce several columns!