June 18, 2014 at 5:05 am
Hi,
just wondering if this can be achieved. If I have 3 rows, and each row has say Description and Amount.
I would like the result set to display only the row 1 Description but also the SUM of all 3 rows in the Amount col?
Is this do able?
thanks,
June 18, 2014 at 5:13 am
Hi, If I have understood your problem correctly, then the solution is grouping of description and sum of Amount.
In this case the example is like this
DescriptionAmount
ABC10
ABC30
ABC60
Please give me example if i am wrong here.
Thanks
Rajneesh
June 18, 2014 at 5:23 am
Hi,
sorry no, if the data was
Desc Amt
bob 10
tim 20
jim 30
then I'm after a result set of
Desc Amt
bob 60
so first row desc but Amt is the total of all rows amounts.
thanks
June 18, 2014 at 5:27 am
CREATE TABLE #Question ([Description] VARCHAR(15), [Amount] INT)
INSERT INTO#Question
SELECT 'Bob', 10 UNION ALL
SELECT 'Tim',20 UNION ALL
SELECT 'Jim',30
SELECT
[desc] =q.[Description]
,amt = SUM(s.totSum)
FROM #Question q
CROSS APPLY
(
SELECT
totSum = SUM(amount) OVER (PARTITION BY q1.Description)
FROM #Question q1
)s
WHERE q.Description = 'Bob'
GROUP BY q.Description
DROP TABLE #Question
Does that do want you want?
Edited to match OP's data
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 18, 2014 at 5:45 am
what about this 😉
;with qry
as
(select [amount],[Description], row_number() over (order by amount) as RN from #question)
select q.[description],sum(q1.amount) from qry q, #Question q1
where RN = 1
group by q.[description]
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
June 18, 2014 at 6:17 am
thanks BWFC that works but instead of
WHERE q.Description = 'Bob'
I'd want the q.Description to be the description of row number 1 as I can't hard code 'Bob' into the sql (as this value would vary depending on other select criteria.
June 18, 2014 at 6:20 am
I see, so you want whatever is the first description value to be the description that is displayed.
How is the ordering done? Is it done on the value of the amount column?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 18, 2014 at 6:22 am
mattech06 (6/18/2014)
thanks BWFC that works but instead ofWHERE q.Description = 'Bob'
I'd want the q.Description to be the description of row number 1 as I can't hard code 'Bob' into the sql (as this value would vary depending on other select criteria.
did you try the query which i posted earlier ?
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
June 18, 2014 at 6:32 am
Rhythmk's solution does exactly what you want or the alteration to my solution below will do the same, although much less elegantly.
SELECT
q.[Description]
,SUM(s.totSum)
FROM #Question q
CROSS APPLY
(
SELECT
totSum = SUM(amount) OVER (PARTITION BY q1.Description)
FROM #Question q1
)s
WHERE q.Amount = (SELECT MIN(amount) FROM #Question)
GROUP BY q.Description
DROP TABLE #Question
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 18, 2014 at 6:55 am
sorry I missed rymthnk post. Yep that also works with the sample db, however my actual working sql is (with user variables included)....
WITH Main as
(
SELECT
DISTINCT t.transactionID, b.TransDate, b.TransType, b.TransDesc1, b.TransDesc2, b.TransDesc3, b.TransDesc4, b.TransDesc5,
(ISNULL(b.Debit, 0) + ISNULL (b.Credit, 0)) as Amount,
ISNULL(r.OrthoID, 0) as OrthoID,
ISNULL(t.PatientID, 0) as PatientID,
t.ledgerAmount, t.LedgerType,
(ISNULL(b.Debit, 0) + ISNULL (b.Credit, 0)) - (t.ledgerAmount * -1) as Variance,
b.month AS Month,
t.Doctor, b.Notes
FROM
WEYBankTransactions b
LEFT JOIN WEYBankRef r on SUBSTRING(b.TransDesc1, 0, 26) = r.BankID
LEFT JOIN WEYTransactions t on r.OrthoID = t.PatientID
AND t.LedgerType in (26,30)
AND t.TransactionDate >= 'May 2014' AND t.TransactionDate <= DATEADD(mm,1,'May 2014')
AND (t.Doctor IN ('') OR '' = '')
WHERE
(r.OrthoID IN ('512427') OR '512427' = '')
AND (b.month IN ('May 2014') OR 'May 2014' = '')
AND (b.TransType IN ('') OR '' = '')
)
so I'm already using a WITH statement so how would I WITH a WITH if I added your sql. Using say just b.TransDesc1 for description and t.LedgerAmount for amount from Main?
June 18, 2014 at 7:00 am
You can use one CTE after another, like so:
CREATE TABLE #Question ([Description] VARCHAR(15), [Amount] INT)
INSERT INTO#Question
SELECT 'Bob', 10 UNION ALL
SELECT 'Tim',20 UNION ALL
SELECT 'Jim',30
;
WITH cteTest AS
(
SELECT * FROM #Question
)
, ----ONLY A COMMA IS NEEDED HERE, NOT ANOTHER WITH
qry
as
(select [amount],[Description]
, row_number() over (order by amount) as RN from #question
)
select q.[description],sum(q1.amount) from qry q, #Question q1
where RN = 1
group by q.[description]
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 18, 2014 at 7:12 am
Brilliant, that works thanks guys.
June 18, 2014 at 7:19 am
You're welcome.
If you have read of this article [/url] then it'll be much quicker getting an answer in future. It'll save people having to make assumptions and it'll be much easier to read and establish what you're after.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 18, 2014 at 7:58 am
Actually guys, the old classic, it worked on that specific example but not on the full data set.
So a better example would be using a table with ..
CREATE TABLE #Question ([Description] VARCHAR(15), [Amount] INT)
INSERT INTO#Question
SELECT 'Bob', 10 UNION ALL
SELECT 'Bob', 20 UNION ALL
SELECT 'Bob', 30 UNION ALL
SELECT 'Jim', 40 UNION ALL
SELECT 'Jim', 50 UNION ALL
SELECT 'Tim', 120
;
with an expected result set of
Bob 60
Jim 90
Tim 120
Hope you haven't gone for lunch 😉
thanks
June 18, 2014 at 7:59 am
mattech06 (6/18/2014)
sorry I missed rymthnk post. Yep that also works with the sample db, however my actual working sql is (with user variables included)....
(b.TransType IN ('') OR '' = '')
Sorry little late but irrespective of ordering on the basis of column you can use more generic code as below.As it may be possible that last row has minimum amount instead of first row.
;with qry
as
(select [amount],[Description], row_number() over (order by (select null)) as RN from #question)
select q.[description],sum(q1.amount) from qry q, #Question q1
where RN = 1
group by q.[description]
Also just out of curiousity, what about above condition ? :unsure:
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply