CODEWITHSUNDEEP
sqlsql-servergroup-bysum
user2617053
user2617053

Reputation: 333

Group by two columns and display grand total in every row

Below are the list data.

Code   ItemCount   Type      Amount 
----------------------------------------
B001    1          Dell         10.00
B001    1          Dell         10.00
B001    1          Apple        10.00
B001    2          Apple        20.00
B001    2          Apple        20.00
B114    1          Apple        30.50
B114    1          Apple        10.00

I need a result to group by code and by type and total the ItemCount and get the grand total of the Amount in every row.

Is this possible?

Code   ItemCount    Type      Amount 
----------------------------------------
B001    2          Dell          20.00
B001    5          Apple         50.00
B114    2          Apple         40.50

Upvotes: 19

Views: 83279

Answers (6)

TechDo
TechDo

Reputation: 18629

Please try:

SELECT
    Code,
    SUM(ItemCount) ItemCount,
    Type,
    SUM(Amount) Amount
FROM
    YourTable
GROUP BY Code, Type
ORDER BY Code

Upvotes: 27

Pooja smithi
Pooja smithi

Reputation: 53

You can try this simpler solution:

select Code,sum(ItemCount),Type,sum(Amount) from table group by code,type

Understanding the 'group by' would come handy

Upvotes: 2

hallie
hallie

Reputation: 2845

If my understanding is correct that the actual total of each row is the product of the itemcount and the amount then you can use the code below. If not use @Abu's code.

;WITH cte AS
(
    SELECT Code, ItemCount, Type, Amount, ItemCount * Amount AS TotalAmount FROM <Table>
)
SELECT 
    Code,
    SUM(ItemCount),
    Type,
    SUM(TotalAmount)
FROM cte
GROUP BY Code, Type

Upvotes: 0

andrewb
andrewb

Reputation: 3095

The amounts you give differ from the sample data but this works for the sample data values:

SELECT Code, SUM(ItemCount) AS ItemCount, [Type], SUM(Amount) AS Amount 
FROM dbo.TestSubs GROUP BY Code,[Type] ORDER BY Code

Upvotes: 0

spencer7593
spencer7593

Reputation: 108410

This looks like homework.

(I swear I thought this was tagged as MySQL when I first looked at the question, but the title clearly shows MS SQL)

For MySQL, this query will return the specified resultset:

SELECT t.Code
     , SUM(t.ItemCount) AS ItemCount
     , t.Type
     , s.Amount AS Amount
  FROM mytable t
 CROSS
  JOIN ( SELECT SUM(r.Amount) AS Amount
           FROM mytable r
       ) s 
 GROUP
    BY t.Code
     , t.Type
 ORDER BY t.Code ASC, t.Type DESC

For other databases, remove For MySQL the backticks from around the column aliases.

If you need to preserve case, for Oracle, identifiers are enclosed in doublequotes. For SQL Server, identifiers are enclosed in square brackets. For MySQL identifiers are enclosed in backticks.

Upvotes: 4

Abubakkar
Abubakkar

Reputation: 15654

You can try this query:

SELECT Code,SUM(ItemCount) AS ItemCount,Type,SUM(Amount) AS Amount
FROM
    table
GROUP BY Code, Type

This will give you proper result. You need to group by Code and Type and not ItemCount

Upvotes: 1

Related Questions