combining 2 rows

Hello,
I have a query that returns 2 rows, which I need to combine into 1 row.
The query looks like this:
SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
CASE
WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Amount (incl. commission)',
CASE
WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Commission'
FROM Reporting.dbo.RollUp t
WHERE (t.PTCode='debit')
AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
ORDER BY t.CName, t.AName, t.ACurrency
...the 2 rows that the query returns are:
CName AName ACurrency Payments Amount (incl. commission) Commission
ClientA ClientA EUR 69 NULL 173.27
ClientA ClientA EUR 69 3465.29 NULL
...and I want to combine those 2 rows into a single row that looks like
this:
CName AName ACurrency Payments Amount (incl. commission) Commission
ClientA ClientA EUR 69 3465.29 173.27
Thanks in advance,
Craig H.Use your query as a derived table and collapse the rows in a query around
it -- like this:
Select CName, AName, ACurrency, Payments, Sum( [Amount (incl. commission)]),
Sum ([Commission])
From
(
SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
CASE
WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Amount (incl. commission)',
CASE
WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Commission'
FROM Reporting.dbo.RollUp t
WHERE (t.PTCode='debit')
AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
ORDER BY t.CName, t.AName, t.ACurrency
) T
Group By CName, AName, ACurrency, Payments
Order By CName, AName, ACurrency
hth,
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company
"Craig H." <spam@.thehurley.com> wrote in message
news:u2%23N1$OnFHA.3336@.tk2msftngp13.phx.gbl...
> Hello,
> I have a query that returns 2 rows, which I need to combine into 1 row.
> The query looks like this:
> SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
> CASE
> WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Amount (incl. commission)',
> CASE
> WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Commission'
> FROM Reporting.dbo.RollUp t
> WHERE (t.PTCode='debit')
> AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
> GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
> ORDER BY t.CName, t.AName, t.ACurrency
>
> ...the 2 rows that the query returns are:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 NULL 173.27
> ClientA ClientA EUR 69 3465.29 NULL
>
> ...and I want to combine those 2 rows into a single row that looks like
> this:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 3465.29 173.27
>
> Thanks in advance,
> Craig H.|||hi
it might work, if u remove
t.TCode from the group by clause
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Craig H." wrote:

> Hello,
> I have a query that returns 2 rows, which I need to combine into 1 row.
> The query looks like this:
> SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
> CASE
> WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Amount (incl. commission)',
> CASE
> WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Commission'
> FROM Reporting.dbo.RollUp t
> WHERE (t.PTCode='debit')
> AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
> GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
> ORDER BY t.CName, t.AName, t.ACurrency
>
> ...the 2 rows that the query returns are:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 NULL 173.27
> ClientA ClientA EUR 69 3465.29 NULL
>
> ...and I want to combine those 2 rows into a single row that looks like
> this:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 3465.29 173.27
>
> Thanks in advance,
> Craig H.
>|||> it might work, if u remove
> t.TCode from the group by clause
No. It will give you the error t.TCCode is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:3485024E-D8DF-42DE-A9F5-13C37C0B22FF@.microsoft.com...
> hi
> it might work, if u remove
> t.TCode from the group by clause
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Craig H." wrote:
>|||Looks like you just need to remove TCode from your GROUP BY list:
...
GROUP BY t.CName, t.AName, t.ACurrency
David Portas
SQL Server MVP
--|||Good catch. I missed it too. So put the CASE expression inside the SUM
aggregate. Something like the following, depending on the desired
result of the SUM:
SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
SUM(CASE WHEN t.TCode = 'debit_batch'
THEN t.LAmount/t.ExchangeRate END) AS 'Amount (incl. commission)',
SUM(CASE WHEN t.TCode = 'commission'
THEN t.LAmount/t.ExchangeRate END) AS 'Commission'
FROM Reporting.dbo.RollUp t
WHERE (t.PTCode='debit')
AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
GROUP BY t.CName, t.AName, t.ACurrency
ORDER BY t.CName, t.AName, t.ACurrency ;
David Portas
SQL Server MVP
--|||David,
Correct me If I am wrong.
As per my understanding , removing TCode fro the Group By list will
throw the error
TCCode is invalid in the select list because it is not contained in
either an aggregate function or
the GROUP BY clause.
SELECT pub_id,
CASE WHEN type = 'business' THEN SUM(ytd_sales) END
FROM Titles
GROUP By pub_id
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1123599468.276266.144040@.g44g2000cwa.googlegroups.com...
> Looks like you just need to remove TCode from your GROUP BY list:
> ...
> GROUP BY t.CName, t.AName, t.ACurrency
> --
> David Portas
> SQL Server MVP
> --
>|||Never mind. I had seen the other post only after sending this.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%23iPyJWPnFHA.764@.TK2MSFTNGP14.phx.gbl...
> David,
> Correct me If I am wrong.
> As per my understanding , removing TCode fro the Group By list will
> throw the error
> TCCode is invalid in the select list because it is not contained in
> either an aggregate function or
> the GROUP BY clause.
>
> SELECT pub_id,
> CASE WHEN type = 'business' THEN SUM(ytd_sales) END
> FROM Titles
> GROUP By pub_id
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1123599468.276266.144040@.g44g2000cwa.googlegroups.com...
>