display all data from row 1 but include sum total of all rows amount column in data set

Question

display all data from row 1 but include sum total of all rows amount column in data set

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 23009 More actions · Answer 1

mattech06 (6/18/2014)
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

That's actually quite different from what you originally asked for. Try this:

SELECT

q.Description

,SUM(q.Amount)

FROM #Question q

GROUP BY q.Description

Nice work on the sample data by the way.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

rhythmk SSCertifiable Points: 7271 More actions · Answer 2

mattech06 (6/18/2014)
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

Here you go

select description,sum(amount) from #Question group by 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
🙂

mattech06 SSCrazy Points: 2484 More actions · Answer 3

that works for the sample but when I apply it to the working sql I get a problem with the group by

;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 '' = '')

)

select m.TransDate, m.TransType, m.TransDesc1, m.TransDesc2, m.TransDesc3, m.TransDesc4, m.TransDesc5, m.Amount, sum(m.ledgerAmount) as ledgerAmount, (m.Amount + sum(m.ledgerAmount)) as Variance, m.Doctor, m.Notes

FROM Main m

group by m.TransDesc1, m.TransType, m.TransDate, m.TransDesc2, m.TransDesc3, m.TransDesc4, m.TransDesc5, m.Amount, m.Variance, m.Doctor, m.Notes

If we used 'bob' it brings back the 3 rows again as I guess the Amount (and Variance) are different and if I don't include those two in the group by I get "Column 'Main.Amount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

Is there a way of having a group by that doesn't have to include all the select columns or an alternative?

thanks,

Neil Burton SSC-Insane Points: 23009 More actions · Answer 4

SELECT distinct

q.Description

,SUM(q.Amount) OVER (PARTITION BY q.Description)

FROM #Question q

It might be worth you trying to create some sample data that's closer to what you actually have. You can do a bit of obfuscation to hide anything confidential and your tables obviously don't need to be as large as your production ones. It will make it much easier to see your datatypes and how things relate to one another.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

mattech06 (6/18/2014)
that works for the sample but when I apply it to the working sql I get a problem with the group by
;WITH Main as
(
SELECT
DISTINCT t.transactionID, b.TransDate, b.TransType, b.TransDesc1, b.TransDesc2, b.TransDesc3, b.TransDesc4, b.TransDesc5,
...
If we used 'bob' it brings back the 3 rows again as I guess the Amount (and Variance) are different and if I don't include those two in the group by I get "Column 'Main.Amount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"
Is there a way of having a group by that doesn't have to include all the select columns or an alternative?
thanks,

You're exposing yourself to a huge risk using DISTINCT in a subquery which you are intending to aggregate. You could lose rows resulting in aggregates which are lower than they should be. If you can identify and quantify genuine duplicates in the output, then you should find an appropriate method of dealing with them, using GROUP BY at the correct grain within the query.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

This might get you started:

SELECT --DISTINCT

t.transactionID, b.TransDate, b.TransType, b.TransDesc1, b.TransDesc2, b.TransDesc3, b.TransDesc4, b.TransDesc5,

(b.Debit + b.Credit) as Amount,

ISNULL(r.OrthoID, 0) as OrthoID,

ISNULL(t.PatientID, 0) as PatientID,

t.ledgerAmount,

t.LedgerType,

(b.Debit + b.Credit) - (t.ledgerAmount * -1) as Variance,

b.[month],

t.Doctor,

b.Notes

FROM (

SELECT

TransDate, TransType, TransDesc1, TransDesc2, TransDesc3, TransDesc4, TransDesc5, Notes, [Month],

Debit = ISNULL(SUM(Debit),0), Credit = ISNULL(SUM(Credit),0)

FROM WEYBankTransactions

WHERE ([month] IN ('May 2014') OR 'May 2014' = '')

AND (TransType IN ('') OR '' = '')

GROUP BY TransDate, TransType, TransDesc1, TransDesc2, TransDesc3, TransDesc4, TransDesc5, Notes, [Month]

) b

LEFT JOIN WEYBankRef r

ON LEFT(b.TransDesc1, 26) = r.BankID

AND (r.OrthoID IN ('512427') OR '512427' = '')

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 '' = '')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mattech06 SSCrazy Points: 2484 More actions · Answer 7

thanks guys. BWFC's last post does appear to work ok but I'm back on it tomorrow with some big testing so take Chris's posts into account then as well.

cheers