i****i
发帖数: 18 | 1 I have a question about T- SQL language.
I wrote a query to get the SUM of every line's Amount with the same
Initiative Key.
SELECT
[Initiative Key]
,[Amount]
,SUM ([Amount]) OVER (PARTITION BY ([Initiative Key])) AS SUM_AMOUNT
FROM [DataClean].[dbo].['Initiative Donations$']
ORDER BY SUM_AMOUNT DESC
Here is the result,
Initiative Key Amount SUM_AMOUNT
KBERFVSJRZIL 10 380
KBERFVSJRZIL 25 380
KBERFVSJRZIL 10 380
KBERFVSJRZIL 10 380
KBERFVSJRZIL 25 380
KBERFVSJRZIL 10 380
KBERFVSJRZIL 10 380
KBERFVSJRZIL 25 380
KBERFVSJRZIL 10 380
KBERFVSJRZIL 10 380
KBERFVSJRZIL 10 380
KBERFVSJRZIL 10 380
KBERFVSJRZIL 25 380
KBERFVSJRZIL 10 380
KBERFVSJRZIL 10 380
KBERFVSJRZIL 10 380
KBERFVSJRZIL 10 380
KBERFVSJRZIL 10 380
KBERFVSJRZIL 10 380
KBERFVSJRZIL 25 380
KBERFVSJRZIL 10 380
KBERFVSJRZIL 10 380
KBERFVSJRZIL 10 380
KBERFVSJRZIL 10 380
97KB07RMROIK 25 375
97KB07RMROIK 25 375
97KB07RMROIK 25 375
97KB07RMROIK 25 375
97KB07RMROIK 25 375
97KB07RMROIK 25 375
97KB07RMROIK 25 375
97KB07RMROIK 25 375
97KB07RMROIK 25 375
97KB07RMROIK 25 375
97KB07RMROIK 25 375
97KB07RMROIK 25 375
97KB07RMROIK 25 375
97KB07RMROIK 25 375
97KB07RMROIK 25 375
but in my final report. I only want to know Initiative Key and SUM_Amount (
removing the duplicates). Is there any way to write those queries in one
file.
Initiative Key SUM_AMOUNT
KBERFVSJRZIL 380
97KB07RMROIK 375 |
B*****g
发帖数: 34098 | 2 http://www.w3schools.com/sql/sql_groupby.asp
【在 i****i 的大作中提到】
: I have a question about T- SQL language.
: I wrote a query to get the SUM of every line's Amount with the same
: Initiative Key.
: SELECT
: [Initiative Key]
: ,[Amount]
: ,SUM ([Amount]) OVER (PARTITION BY ([Initiative Key])) AS SUM_AMOUNT
: FROM [DataClean].[dbo].['Initiative Donations$']
: ORDER BY SUM_AMOUNT DESC
: Here is the result,
|
s**********o
发帖数: 14359 | 3 你QUERY写错了吧
SELECT
[Initiative Key]
,SUM ([Amount])
FROM [DataClean].[dbo].['Initiative Donations$']
GROUP BY [Initiative Key] |
i****i
发帖数: 18 | 4 Thank you Beijing. It is very easy to use Group By. I always think of
Partition .....
【在 B*****g 的大作中提到】
: http://www.w3schools.com/sql/sql_groupby.asp
|
i****i
发帖数: 18 | 5 Thank you very much. It works with Group BY.
【在 s**********o 的大作中提到】
: 你QUERY写错了吧
: SELECT
: [Initiative Key]
: ,SUM ([Amount])
: FROM [DataClean].[dbo].['Initiative Donations$']
: GROUP BY [Initiative Key]
|
y****w
发帖数: 3747 | 6 if partition, remove amount, plus distinct.
【在 i****i 的大作中提到】
: I have a question about T- SQL language.
: I wrote a query to get the SUM of every line's Amount with the same
: Initiative Key.
: SELECT
: [Initiative Key]
: ,[Amount]
: ,SUM ([Amount]) OVER (PARTITION BY ([Initiative Key])) AS SUM_AMOUNT
: FROM [DataClean].[dbo].['Initiative Donations$']
: ORDER BY SUM_AMOUNT DESC
: Here is the result,
|
