Group by two columns and display grand total in every row

Question

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 

Answer 1

Please try:

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

Answer 2

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.