summary by customer - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

summary by customer

Author

Topic

Gekko
Yak Posting Veteran

63 Posts

Posted - 2011-08-25 : 09:48:10

Hallo all.....

I have following the problem. I want select summary by customer and select oldest first invoice.

TabInvoices

Num....customer....invoice....date.......sum
1......philips......12011..... 1.1.2011....5
1......philips......22011.... .4.1.2011....2
1......philips......32011......6.1.2011....4
2......sony.........42011......4.3.2011....9
2......sony.........52011... ..6.3.2011....3
2......sony.........62011.. ...8.3.2011....4
3......dell.........72011......1.5.2011....4
3......dell.........82011......5.5.2011....2
3......dell.........92011......10.5.2011...1

result:

Num....customer....invoice....date.......sum
1......philips......12011....1.1.2011.....11
2......sony.........42011....4.3.2011.....16
3......dell.........72011....1.5.2011.....7

philips have oldest invoice 12011 and sum all invoices for philips is 11
dell have oldest invoice 72011 and sum all invoices for dell is 7

thanks all...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-25 : 11:09:05

[code]
DECLARE @sample TABLE (
[Num] INT
, [Customer] VARCHAR(15)
, [Invoice] INT
, [Date] DATE
, [Sum] INT
)

INSERT @sample ([Num], [Customer], [Invoice], [Date], [Sum])
SELECT 1, 'philips', 12011, '1.1.2011', 5
UNION SELECT 1, 'philips', 22011, '4.1.2011', 2
UNION SELECT 1, 'philips', 32011, '6.1.2011', 4
UNION SELECT 2, 'sony', 42011, '4.3.2011', 9
UNION SELECT 2, 'sony', 52011, '6.3.2011', 3
UNION SELECT 2, 'sony', 62011, '8.3.2011', 4
UNION SELECT 3, 'dell', 72011, '1.5.2011', 4
UNION SELECT 3, 'dell', 82011, '5.5.2011', 2
UNION SELECT 3, 'dell', 92011, '10.5.2011', 1

SELECT * FROM @sample

SELECT
[Num]
, [Customer]
, MIN([Invoice]) AS [FirstInvoice]
, MIN([Date]) AS [FirstDate]
, SUM([Sum]) AS [SummedSum]
FROM
@sample
GROUP BY
[Num]
, [Customer]
ORDER BY
[Num]
[/code]
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-25 : 11:27:17

Results:


Num         Customer        FirstInvoice FirstDate  SummedSum
----------- --------------- ------------ ---------- -----------
1           philips         12011        2011-01-01 11
2           sony            42011        2011-04-03 16
3           dell            72011        2011-01-05 7

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Gekko
Yak Posting Veteran

63 Posts

Posted - 2011-08-25 : 12:08:51

thanks ... Transact Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources