Adding Unique data

Dear Folks,
I have a table like
Invno Amount
55 100
55 200
56 50
57 150
57 300
57 50
I need to query to make it as
55 - 300
56 - 50
57 - 500
Please help me...
Thanks for your replies in advance.
Regards,
Selvarathinam.SELECT InvNo, SUM(Amount)
FROM Table
GROUP BY InvNo
Selvarathinam wrote:
> Dear Folks,
> I have a table like
> Invno Amount
> 55 100
> 55 200
> 56 50
> 57 150
> 57 300
> 57 50
> I need to query to make it as
> 55 - 300
> 56 - 50
> 57 - 500
> Please help me...
> Thanks for your replies in advance.
> Regards,
> Selvarathinam.|||Select invno, sum(amount) group by invno
"Selvarathinam" <s.selvarathinam@.gmail.com> wrote in message
news:1149799832.127814.236410@.y43g2000cwc.googlegroups.com...
> Dear Folks,
> I have a table like
> Invno Amount
> 55 100
> 55 200
> 56 50
> 57 150
> 57 300
> 57 50
> I need to query to make it as
> 55 - 300
> 56 - 50
> 57 - 500
> Please help me...
> Thanks for your replies in advance.
> Regards,
> Selvarathinam.
>|||Thanks a ton...
itz working
Tracy McKibben wrote:
> SELECT InvNo, SUM(Amount)
> FROM Table
> GROUP BY InvNo
>
> Selvarathinam wrote:

adding some rows to a select

Hi folks,

I've a sql query problem I was wondering if you all had a quick and
dirty solution for. I've a query:

Select code, value from table_a where date in
(2004) and a_code in ('1000','2000') and b_code in ('01000','02000')

This returns a table that looks like:

A_CODE B_CODE VALUE
-- -- --

1000 01000 $500
1000 02000 $750

What I'd like to see is:

A_CODE B_CODE VALUE
-- -- --

1000 01000 $500
1000 02000 $750
2000 01000 $0
2000 02000 $0

Any suggestions on how to rewrite my query so the results show A_CODE
2000 with a VALUE of 0 or null?

Thank much in advance!

MarcMarc (brownjenkn@.aol.com) writes:
> I've a sql query problem I was wondering if you all had a quick and
> dirty solution for. I've a query:
> Select code, value from table_a where date in
> (2004) and a_code in ('1000','2000') and b_code in ('01000','02000')
> This returns a table that looks like:
> A_CODE B_CODE VALUE
> -- -- --
> 1000 01000 $500
> 1000 02000 $750
> What I'd like to see is:
> A_CODE B_CODE VALUE
> -- -- --
> 1000 01000 $500
> 1000 02000 $750
> 2000 01000 $0
> 2000 02000 $0
> Any suggestions on how to rewrite my query so the results show A_CODE
> 2000 with a VALUE of 0 or null?

CREATE TABLE a_code (a_code char(4) NOT NULL
CREATE TABLE b_code (b_code char(5) NOT NULL

go
INSERT a_code (a_code) VALUES ('1000')
INSERT a_code (a_code) VALUES ('2000')
INSERT b_code (b_code) VALUES ('01000')
INSERT b_code (b_code) VALUES ('02000')
go
SELECT a.a_code, b.b_code, coalesce(t.value, 0)
FROM (a_code a
CROSS JOIN b_code b)
LEFT JOIN table_a t ON a.a_code = t.a_code
AND b.b_code = t.b_code
ABD t.date = '2004'

Here I am handling a_code and b_code in the same way, so you will
get output for missing b_codes as well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp