Group by two columns and display grand total in ev

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

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

Answer 2

Please try:

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

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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