CODEWITHSUNDEEP
sql-server-2008t-sql
user3219693
user3219693

Reputation: 201

SQL Query - Select with a total column

I have a query that returns rows for current and non-current items. The current items have no value The non-current items do have a value

I need my query to return all the current values AND the total value of the non-current items:

Sample:

Name   Status       Value
KB     Current      0
KB     Non-Current  5
KB     Current      0
KB     Non-Current  5
KB     Non-Current  5
KB     Current      0
KB     Current      0
KB     Current      0
AN     Non-Current  5
AN     Current      0
AN     Non-Current  5
AN     Current      0
AN     Current      0

I would need:

Name   Status       Value  TotalValue
KB     Current      0      15
KB     Current      0      15
KB     Current      0      15
KB     Current      0      15
KB     Current      0      15
AN     Current      0      10
AN     Current      0      10
AN     Current      0      10

I've tried 

SELECT Name,Status,Value,(SELECT SUM(Value)
FROM Table

but obviously this totals all the values rather than per name, if I try to GROUP BY Name then I get the error this returns more than 1 value.

Upvotes: 0

Views: 72

Answers (3)

Tim Schmelter
Tim Schmelter

Reputation: 460288

this totals all the values rather than per name, if I try to GROUP BY Name

You can use SUM with OVER clause:

SELECT Name, Status, Value,
       SumPerName = SUM(Value) OVER (PARTITION BY NAME)
FROM Table

Demo

(your sample data is bad since all names are same)

If you actually want to partition by Status(which makes your sample data better) you just have to replace PARTITION BY NAME with PARTITION BY Status.

If you only want records with Status='Current' you just have to apply the WHERE:

SELECT Name, Status, Value,
       SumPerName = SUM(Value) OVER (PARTITION BY NAME)
FROM Table
WHERE Status = 'Current'

Edit: use a CTE and apply the WHERE in the outer query:

WITH CTE AS
(
  SELECT Name, Status, Value,
         SumPerName = SUM(Value) OVER (PARTITION BY NAME),
         Total = (SELECT SUM(Value) FROM Table1)
  FROM Table1
)
SELECT * FROM CTE
WHERE Status = 'Current'

new Demo (with sample data that contains two different names)

Upvotes: 2

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

select t1.[Name], t1.[Status], t1.[Value], t2.TotalValue
from Table1 t1
left join (select t.[Name],isnull(sum(t.[Value]),0) as TotalValue 
            from Table1 t
            where t.[Status]='Non-Current'
            group by [Name]
            )t2 on t1.Name=t2.Name
where t1.[Status]='Current'

SQl Fiddle

Upvotes: 0

KekuSemau
KekuSemau

Reputation: 6852

TSQL offers window functions for that:

SELECT
    Name
    , Status
    , Value
    , SUM(Value) OVER (PARTITION BY Status) AS SumByStatus
    , SUM(Value) OVER (PARTITION BY Name) AS SumByNAme
    , SUM(Value) OVER () AS SumOverAllReturnedRows
FROM T

MSDN

I think you need to partition by Status. Your example is a bit confusing because Name is the same everywhere and the Value is 0 for all Status=Current values anyway.

Upvotes: 0

Related Questions