CODEWITHSUNDEEP
sqlsql-server
alexistkd
alexistkd

Reputation: 898

Join and sum values in a SQL Server view

I'm trying to achieve this: I have a view in SQL Server 2012, and the result set is like this:

id sku  quantity  fieldtax  tax2
--------------------------------
1  2607   1        10         7
2  2607   2        10         7
3  55612  5         7        15

I would like to make the result set look like this:

id sku  quantity  fieldtax  tax2
---------------------------------
1  2607   3        10        7
3  55612  5         7       15

I need to sum the columns that have the same sku - how can I achieve this?

I already try this is the full table:

SELECT
    VENTAS_AGRUPADAS.id,
    VENTAS_AGRUPADAS.TransactionType,
    VENTAS_AGRUPADAS.TransactionModifier,
    VENTAS_AGRUPADAS.SKU,
    VENTAS_AGRUPADAS.Tax1,
    VENTAS_AGRUPADAS.Tax2,
    SUM(VENTAS_AGRUPADAS.Quantity) AS QUANTITY,
    VENTAS_AGRUPADAS.Tax3,
    VENTAS_AGRUPADAS.TransactionNumber
FROM 
    dbo.VENTAS_AGRUPADAS
GROUP BY 
    VENTAS_AGRUPADAS.id,
    VENTAS_AGRUPADAS.TransactionType,
    VENTAS_AGRUPADAS.TransactionModifier,
    VENTAS_AGRUPADAS.SKU,
    VENTAS_AGRUPADAS.Tax1,
    VENTAS_AGRUPADAS.Tax2,
    VENTAS_AGRUPADAS.Tax3,
    VENTAS_AGRUPADAS.TransactionNumber
ORDER BY 
    VENTAS_AGRUPADAS.SKU DESC

Thank you

Upvotes: 0

Views: 158

Answers (2)

Samuel Rocha García
Samuel Rocha García

Reputation: 128

You need a combination of SUM and GROUP BY.

SELECT id, sku, SUM(quantity) as quantity, SUM(fieldtax) as fieldtax, tax2 FROM tabla GROUP BY sku;

Looking at your query, you're grouping by too many different columns, no good can come from that. Only group by the columns that can have a repeated value among different rows, like the sku as you were saying.

Upvotes: 1

jarlh
jarlh

Reputation: 44766

Perhaps this, if fieldtax and tax2 always will be the same for an sku:

select sku, SUM(quantity), fieldtax, tax2
from tablename
group by sku, fieldtax, tax2

Or this, for each sku, sum quantity, and take average for fieldtax and tax2:

select sku, SUM(quantity), AVG(fieldtax), AVG(tax2)
from tablename
group by sku

Upvotes: 4

Related Questions