Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 help  [new]
puss_in_boots
Member

Откуда: ⒹⓌ(DreamWorks)
Сообщений: 93
Hello
This is my Database...i want to group and sum the amount and update in one row..(everythings will be in one row
'1 can only group using query but it wasnt update...still they are in different rows

SELECT DISTINCTROW STD.Name, STD.Classes, Sum(STD.Amount) AS [Sum]
FROM STD
GROUP BY STD.Name, STD.Classes (im uisng this code for grouping


К сообщению приложен файл. Размер - 13Kb
21 ноя 12, 07:39    [13504615]     Ответить | Цитировать Сообщить модератору
 Re: help  [new]
kryak
Member

Откуда:
Сообщений: 396
What would you want in result?

Something like this?

ID Name Classes Amount
8 John A 22
9 John A 22
10 John A 22
11 John A 22

Or this

ID Name Classes Amount
? John A 22

In first case, may be (of course if you don't have nay NULL values in Name and Classes columns):
UPDATE s1
SET
Amount = s2.[Sum]
FROM STD AS s1
INNER JOIN ( SELECT STD.Name, STD.Classes, Sum(STD.Amount) AS [Sum]
                    FROM STD
                    GROUP BY STD.Name, STD.Classes
                  ) AS s2 ON s1.Name = s2.Name AND s1.Classes = s2.Classes
21 ноя 12, 08:44    [13504730]     Ответить | Цитировать Сообщить модератору
 Re: help  [new]
puss_in_boots
Member

Откуда: ⒹⓌ(DreamWorks)
Сообщений: 93
kryak,

the result is Name Classes Sum
JOHN A 22 in grouoing and i want to update exactly how it shows in grouping....and have one unique number ...beacuse in grouping..still they are having different uniQue no.
21 ноя 12, 09:40    [13504906]     Ответить | Цитировать Сообщить модератору
 Re: help  [new]
kryak
Member

Откуда:
Сообщений: 396
If you are using MS SQL 2008 or higher, than

DECLARE @std TABLE (ID int, Name varchar(50), Classes varchar(50), Amount int)
INSERT INTO @std VALUES (1, 'John', 'A', 1), (2, 'John', 'A', 1), (3, 'John', 'A', 10), (4, 'John', 'A', 10), (5, 'Mark', 'B', 1)


SELECT  *
FROM    @std;

MERGE @std AS s1
USING ( SELECT s.Name, s.Classes, Sum(s.Amount) AS [Sum], MIN(s.ID) AS ID
        FROM @std AS s
        GROUP BY s.Name, s.Classes
      ) AS s2
ON  s1.ID = s2.ID
    WHEN MATCHED THEN
                    UPDATE SET Amount = s2.[Sum]
    WHEN NOT MATCHED BY SOURCE THEN DELETE
;

SELECT  *
FROM    @std;
21 ноя 12, 11:04    [13505456]     Ответить | Цитировать Сообщить модератору
 Re: help  [new]
puss_in_boots
Member

Откуда: ⒹⓌ(DreamWorks)
Сообщений: 93
kryak,

Thank you.
22 ноя 12, 05:45    [13511040]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить