June 2, 2010 at 11:48 am
I need some guidance on a subselect (or maybe a better way to handle). I need to add the quantity in a column when they are in certain accounts on a date range entered by user.
TEST Table\Data:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'#temptable') IS NOT NULL
DROP TABLE #temptable
CREATE TABLE #temptable
(
[id] [int] NOT NULL,
[transdate] [datetime] NOT NULL,
[account] [nvarchar](32) NOT NULL,
[username] [nvarchar](64) NULL,
[quantity] [int] NOT NULL,
[grossamt] [money] NOT NULL,
[netamt] [money] NOT NULL,
[workcode] [nvarchar](16) NULL
)
GO
INSERT INTO #temptable (id, transdate, account, username, quantity, grossamt, netamt, workcode)
SELECT 1, '5/3/2010 2:45:17 PM', 000000, 'Nobody, A. Joe', 42, 4.2000, 0.0000, 'B/W' UNION ALL
SELECT 2, '5/3/2010 8:12:14 AM', 000000, 'Nobody, A. Joe', 42, 4.2000, 0.0000, 'B/W' UNION ALL
SELECT 3, '5/3/2010 8:23:17 AM', 000000, 'Nobody, A. Joe', 20, 2.0000, 0.0000, 'B/W' UNION ALL
SELECT 4, '5/3/2010 9:41:35 AM', 300949, 'Nobody, A. Joe', 1, 0.1000, 0.1000, 'B/W' UNION ALL
SELECT 5, '5/3/2010 9:51:39 AM', 300949, 'Nobody, A. Joe', 3, 0.3000, 0.3000, 'B/W' UNION ALL
SELECT 6, '5/3/2010 10:32:19 AM', 300952, 'Nobody, A. Joe', 3, 0.3000, 0.3000, 'B/W' UNION ALL
SELECT 7, '5/3/2010 10:36:36 AM', 301836, 'Somebody, A. Joe', 6, 0.6000, 0.6000, 'B/W' UNION ALL
SELECT 8, '5/3/2010 11:01:14 AM', 0100, 'Somebody, A. Joe', 8, 0.8000, 0.0000, 'B/W' UNION ALL
SELECT 9, '5/3/2010 11:54:41 AM', 0100, 'Somebody, A. Joe', 12, 1.2000, 0.0000, 'B/W' UNION ALL
SELECT 10, '5/4/2010 9:55:29 AM', 000000, 'Somebody, A. Joe', 1, 0.1000, 0.0000, 'B/W' UNION ALL
SELECT 11, '5/4/2010 2:58:54 PM', 301832, 'Somebody, A. Joe', 1, 0.1000, 0.1000, 'B/W' UNION ALL
SELECT 12, '5/5/2010 1:07:39 PM', 000000, 'Somebody, A. Joe', 9, 0.9000, 0.0000, 'B/W'
--select * from #temptable
--drop table #temptable
SQL Query in BIDS Query Designer:
SELECT id, transdate AS begindate, account, username, quantity, grossamt, netamt, transdate AS enddate, workcode, transdate
FROM #temptable
WHERE transdate BETWEEN @begindate AND @endate +1
SubSelect Query:
SELECT SUM(quantity) AS NBTotal
FROM #temptable
WHERE (transdate > @begindate) AND (transdate < @endate) AND (account IN (N'000000', N'0100', N'0300', N'0400', N'0500', N'0600', N'0700', N'0900'))
GROUP BY username
I combined them to get this:
SELECT id, transdate AS begindate, account, username, quantity, grossamt, netamt, transdate AS enddate, workcode, transdate,
(SELECT SUM(quantity) AS NBTotal
FROM #temptable
WHERE (account IN (N'000000', N'0100', N'0300', N'0400', N'0500', N'0600', N'0700', N'0900'))
GROUP BY username) AS NBTotal
FROM #temptable AS #temptable_1
WHERE (transdate > @begindate) AND (transdate < @endate)
GROUP BY uid, transdate, account, username, quantity, grossamt, netamt, workcode
When I run the combined one, I get an error: Subquery returned more than 1 value. This is not permitted.... or when subquery is used as an expression.
Does the error mean literally only 1 value or 1 value per row?
If it can only return 1 value, is there another way to handle the SUM expression?
June 2, 2010 at 12:35 pm
The problem is your GROUP BY username condition within your subselect.
This may cause more than one value (e.g. two NBTotal's if there are two username) leading to the error you describe.
The question is: What are you intended to do?
Maybe you need to move the subquery in the FROM section (as a derived table) and join it on username (hoping that you have a concept to make those values unique...).
Please clarify what you're looking for.
June 2, 2010 at 12:45 pm
I am trying to return a single value per user (the sum of the quantity where the account = <the list>). When I run the query in a seperate dataset, it returns 1 value per user as I wanted/expected. I was hoping I could use the subselect to tally the value in a column.
The table I am using to pull the subselect is the same table as the main report so have access to unique key or user name to JOIN with...
Does that make since?
June 2, 2010 at 12:50 pm
I was looking at the query and had a thought... could the issue be that the main query returns multiple rows per user while the subselect only has 1?
June 2, 2010 at 12:57 pm
Let's see if the following code will give the requested result:
SELECT id, transdate AS begindate, account, #temptable_1.username, quantity, grossamt, netamt, transdate AS enddate, workcode, transdate,NBTotal
FROM #temptable AS #temptable_1
LEFT OUTER JOIN
(SELECT SUM(quantity) AS NBTotal,username
FROM #temptable
WHERE (account IN (N'000000', N'0100', N'0300', N'0400', N'0500', N'0600', N'0700', N'0900'))
GROUP BY username) AS NBTotalQry
ON NBTotalQry.username=#temptable_1.username
WHERE (transdate > @begindate) AND (transdate < @endate)
I'm not sure if you either need to remove the WHERE condition from the subquery or if the sample data really will lead to zero result sets. But at least it should give you something to start with.
If you need further assistance please provide the expected result based on your sample data including an information whether the WHERE condition is required or not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply