December 6, 2011 at 5:07 pm
I have the following tables and query:
DECLARE @Employee TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](25) NOT NULL);
DECLARE @ExampleTable TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [int] NOT NULL,
[SomeValue] [int] NOT NULL);
INSERT INTO @EMPLOYEE (Name) VALUES ('Steve');
INSERT INTO @EMPLOYEE (Name) VALUES ('Bob');
INSERT INTO @ExampleTable
(EmployeeID, SomeValue)
VALUES
(1, 3),
(1, 4),
(1, 5),
(2, 2),
(2, 2),
(2, 2);
When I am just trying to query the data for 1 person it works fine:
SELECT
E.Name,
(SELECT 100 - SUM(CASE WHEN ET.SomeValue > 1 THEN ET.SomeValue ELSE 0 END) AS TestColumn
FROM
@ExampleTable ET RIGHT JOIN @Employee E ON ET.EmployeeID = E.ID
WHERE
E.ID IN ('1')
GROUP BY ET.EmployeeID)
FROM
@ExampleTable ET RIGHT JOIN @Employee E ON ET.EmployeeID = E.ID
WHERE
ET.EmployeeID IN ('1')
GROUP BY E.Name
However, I need the ability to query multiple people, like this:
SELECT
E.Name,
(SELECT 100 - SUM(CASE WHEN ET.SomeValue > 1 THEN ET.SomeValue ELSE 0 END) AS TestColumn
FROM
@ExampleTable ET RIGHT JOIN @Employee E ON ET.EmployeeID = E.ID
WHERE
E.ID IN ('1','2')
GROUP BY ET.EmployeeID)
FROM
@ExampleTable ET RIGHT JOIN @Employee E ON ET.EmployeeID = E.ID
WHERE
ET.EmployeeID IN ('1','2')
GROUP BY E.Name
This throws an error 'subquery returned more than 1 value'.
I understand why it's throwing the error, but I don't know how I can change my subquery so it returns 1 value, and still performs the calculation for each user queried.
The results should be as follows:
Name TestColumn
Steve 88
Bob 94
Any suggestions?
Thanks in advance!
December 6, 2011 at 8:07 pm
I think you your query could be rewritten as :
SELECT
E.Name,
100 - SUM(CASE WHEN ET.SomeValue > 1 THEN ET.SomeValue ELSE 0 END) AS TestColumn
FROM
@ExampleTable ET RIGHT JOIN @Employee E ON ET.EmployeeID = E.ID
WHERE
ET.EmployeeID IN ('1','2')
GROUP BY E.Name
December 6, 2011 at 9:54 pm
Thanks happycat, that worked great!
This leads me to another question now.
How can I update in a similar fashion:
UPDATE @ExampleTable SET
"SomeValue" = 100 - (SELECT SUM(CASE WHEN ET.SomeValue > 1 THEN ET.SomeValue ELSE 0 END) AS TestColumn)
FROM
@ExampleTable ET RIGHT JOIN @Employee E ON ET.EmployeeID = E.ID
WHERE
ET.EmployeeID IN ('1') AND ET.EmployeeID = E.ID
This throws an error 'An aggregate may not appear in the set list of an Update statement'
December 7, 2011 at 12:11 am
pls try below code
Update T set T.SomeValue=K.TestColumn
FROM @ExampleTable T
INNER JOIN
(SELECT E.ID,
E.Name,
100 - SUM(CASE WHEN ET.SomeValue > 1 THEN ET.SomeValue ELSE 0 END) AS TestColumn
FROM
@ExampleTable ET RIGHT JOIN @Employee E ON ET.EmployeeID = E.ID
WHERE
ET.EmployeeID IN ('1','2')
GROUP BY E.Name,E.ID)k
ON T.EmployeeID=K.ID
December 7, 2011 at 1:46 am
TRY...
SELECT
E.NAME,
100 - SUM(ISNULL(ET.SOMEVALUE,0)) AS TESTCOLUMN
FROM
@EXAMPLETABLE ET RIGHT JOIN @EMPLOYEE E ON ET.EMPLOYEEID = E.ID
WHERE
ET.EMPLOYEEID IN ('1','2')
GROUP BY E.NAME
December 7, 2011 at 1:47 am
TRY THIS
SELECT
E.NAME,
100 - SUM(ISNULL(ET.SOMEVALUE,0)) AS TESTCOLUMN
FROM
@EXAMPLETABLE ET RIGHT JOIN @EMPLOYEE E ON ET.EMPLOYEEID = E.ID
WHERE
ET.EMPLOYEEID=E.ID
GROUP BY E.NAME
December 7, 2011 at 2:01 am
I prefer to use a CTE for such updates: it makes the code more readable and maintainable. Plus you can quickly test the result of the CTE by using a select instead of the update, which will give a better indication of the data you're working with.
with cteUpdate as
(
select sum(case when [ET].[SomeValue] > 1 then [ET].[SomeValue] else 0 end) as [TestColumn]
, [E].[ID]
from @ExampleTable ET
right outer join @Employee E on [ET].[EmployeeID] = [E].[ID]
where [ET].[EmployeeID] in ('1')
group by [E].[ID]
)
update @ExampleTable
set [SomeValue] = 100 - [upd].[TestColumn]
from [cteUpdate] as upd
where[EmployeeID] = upd.[ID];
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.
You can find me on LinkedIn.
I support The Programmer's Bill of Rights.
MCITP, MCDBA, MCSD
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply