• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

How can I view detalis and sum info in same query

Hello, I have one table:
[ID]      [Group_ID]       [Amount]
1      1      10
2      1      20
3      2      5


I want to make a query that show details, but also sum by Group_ID.

[ID]       [Amount]
1      10
2      20
      30 (Sum group id 1)
3      5
      5 (Sum group id 2)

Can some help me? (I use MS SQL server 2008 R2)
0
tselectro
Asked:
tselectro
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Here's an example from an article I wrote on SQL Server GROUP BY solutions.
Scroll down to #5.

In your case, where the example has a subquery that returns the Max(), you'll need a subquery that returns a Sum().

Something like..
SELECT yt.ID, yt.Group_ID, yt.Amount, yt_total.the_sum
FROM your_table yt
JOIN (
   SELECT Group_ID, Sum(Amount) as the_sum
   FROM your_table
   GROUP BY Group_ID ) yt_total ON yt.Group_ID = yt_total.Group_ID

Open in new window

Guessing for readability it would be better to include the Group_ID in the query results.
0
 
Scott PletcherSenior DBACommented:
SELECT
    CASE WHEN ID_ = 'Total' THEN '' ELSE ID_ END AS ID, Amount
FROM (
    SELECT CAST(ID AS varchar(10)) AS ID_, Group_ID, Amount
    FROM dbo.tablename
    UNION ALL
    SELECT 'Total', Group_ID, SUM(Amount)
    FROM dbo.tablename
    GROUP BY Group_ID
) AS derived
ORDER BY
    Group_ID, ID_
0
 
tselectroAuthor Commented:
Thank you very much!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now