CODEWITHSUNDEEP
sqlsql-server-2008
Deepak Kumar Padhy
Deepak Kumar Padhy

Reputation: 4388

How to find distinct sum of a column in sql server?

I have a table like this structure:

 lineID  lineItemPrice
    1            20
    2            25
    3            27
    4            30
    4            30 
    4            30

I want to get the sum of lineItemPrice where lineId is distinct. I am not sure what should be sql query? Please help.

The output should be 102.

Upvotes: 0

Views: 51

Answers (5)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Why Not this

SELECT * FROM 
(
Select  lineID, SUM(lineItemPrice) Over(Partition By lineID) Total 
From TableName 
  ) T
Group By lineID,Total

Fiddle Demo

Output:

  lineID     lineItemPrice
    1            20
    2            25
    3            27
    4            90

Upvotes: 0

TechDo
TechDo

Reputation: 18629

Please try:

select 
    SUM(lineItemPrice) lineItemPrice 
from(
    select 
        distinct lineID, lineItemPrice 
    From tbl
)x

You can use below query if lineID lineITemPrice for a same value pair.

select
    SUM(DISTINCT lineItemPrice) lineItemPrice 
From tbl

Upvotes: 1

crthompson
crthompson

Reputation: 15865

I cant quite tell if you are looking for this:

select
   sum(lineItemPrice), lineID
from
   table
group by lineID

Or this:

select
   sum(lineItemPrice)
from
   (select distinct lineID, lineItemPrice from table)

If you want the sum of the whole table:

select
   sum(lineItemPrice)
from
   table

The first would give results that would sum up all the lineItemPrice's for their respective lineID's

 lineID  lineItemPrice
    1            20
    2            25
    3            27
    4            90

The second would sum all these distinct records giving 102 as the answer

 lineID  lineItemPrice
    1            20
    2            25
    3            27
    4            30

The third:

lineItemPrice
   162

Upvotes: 1

Raging Bull
Raging Bull

Reputation: 18747

Try this:

SELECT SUM(lineItemPrice) as TotalSum FROM
(SELECT lineItemPrice
FROM TableName
GROUP BY lineID,lineITemPrice) T

Result:

TOTALSUM
102

See result in SQL Fiddle.

Upvotes: 1

NKR
NKR

Reputation: 2943

This should work :

Select  lineID, SUM(lineItemPrice) Total From YourTableName Group By lineID

Very Simple.

The GROUP BY statement is used in conjunction with the aggregate functions (we used SUM function here) to group the result-set by one or more columns.

Upvotes: 0

Related Questions