Query to Summarize

Question

• Hi All,

I have a table with detailed records in a table.

For example,

col1 col2 col3 col4 benefit

1236  30  Peter Samuel 40.00
1236  30  Peter Samuel 50.00

I want to summarize the single value col1 whose one single records 1236 and sum of the benefit like 90.00

This should be applied for the entire table.  Also, I need all the columns in the result set.

Any help!!!

Regards,
kccrga

Tuesday, September 24, 2013 10:45 PM

• You may use OVER clause to aggregate (SUM) -

```SELECT DISTINCT Col1
,Col2
,Col3
,Col4
,SUM(benefit) OVER (PARTITION BY Col1) AS TotalBenefi
FROM @tab```

Narsimha

Tuesday, September 24, 2013 10:56 PM
• You'll have to make a choice what you want for col3 en col4. Here is one solution:

```SELECT col1,
col2,
MIN(col3) AS col3,
MIN(col4) AS col4,
SUM(benefit) AS total_benefits
FROM my_table
GROUP BY col1, col2
```
Naarashimha's solution makes a different choice about those columns (or rather: rows where those columns have different values).

Gert-Jan

Wednesday, September 25, 2013 9:25 AM

All replies

• You may use OVER clause to aggregate (SUM) -

```SELECT DISTINCT Col1
,Col2
,Col3
,Col4
,SUM(benefit) OVER (PARTITION BY Col1) AS TotalBenefi
FROM @tab```

Narsimha

Tuesday, September 24, 2013 10:56 PM
• You'll have to make a choice what you want for col3 en col4. Here is one solution:

```SELECT col1,
col2,
MIN(col3) AS col3,
MIN(col4) AS col4,
SUM(benefit) AS total_benefits
FROM my_table
GROUP BY col1, col2
```
Naarashimha's solution makes a different choice about those columns (or rather: rows where those columns have different values).

Gert-Jan

Wednesday, September 25, 2013 9:25 AM