sum field based on criteria

[rrmcguire]

Hello I have a bunch of records which the amounts of those are needing to be summed based on their source.

i.e.

sum(amount) where source = 'DRS'

or sum(amount) where source = 'CASH', etc.

so basically I have a group of records

SUM

​

SOURCE

1000

​

DRS
2000

​

DRS
3000

​

CASH
4000

​

CASH
5000

​

CASH

how would I get the values summed together for all records of source CASH


thanks

 

[bborissov]

SELECT ...
       SUM(CASE WHEN source = 'CASH' THEN Amount END) AS CashSum
...

Borislav Borissov
VFP9 SP2, SQL Server

 

[rrmcguire]

I have it written as this

select
SUM((CASE WHEN `source` = 'CASH' THEN `amount`)END) AS CashSum

and it doesn't seem to be working

 

[philhege]

This is a perfect case for GROUP BY.

USE [Test]
GO

/****** Object:  Table [dbo].[SumTest]    Script Date: 04/20/2016 12:52:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SumTest]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SumTest](
	[amount] [int] NULL,
	[source] [varchar](8) NULL
) ON [PRIMARY]
END
GO

SET ANSI_PADDING OFF
GO


--Populate
insert into sumtest
select 1000,'DRS'
UNION SELECT 2000,'DRS'
UNION SELECT 3000,'CASH'
UNION SELECT 4000,'CASH'
UNION SELECT 5000,'CASH'

--Query
select sum(amount), source
from sumtest 
group by source

-----------
With business clients like mine, you'd be better off herding cats.

 

[rrmcguire]

I Already have all of the data created so I basically need to know how to sum the amount based on the record have a different source type

 

[philhege]

I included the DDL and insert query to serve as a complete example. The query in my code block performs a SUM of the amount for each type of source. Did you try that? Maybe I'm not understanding what you're trying to accomplish.

-----------
With business clients like mine, you'd be better off herding cats.

 

[rrmcguire]

have it written like this with gl_account as the table I'm pulling from, and it is not working?

select SUM(`amount`),`source`
from gl_ledger
group by `source`

 

[Andrzejek]

Is this really how your data looks?
[tt]SUM
SOURCE

1000
DRS
2000
DRS
3000
CASH
4000
CASH
5000
CASH
[/tt]
Or do you have something like this:

[pre]
SUM SOURCE
1000 DRS
2000 DRS
3000 CASH
4000 CASH
5000 CASH
[/pre]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[rrmcguire]

yes, Andy the way you have it is correct...there are a lot more records than just 5 but what we're trying to do is get the sum of the amounts as an example if the source = 'CASH'

 

[micang]

Remove the apostrophes

select SUM(amount),source
from gl_ledger 
group by source

Thanks
Michael

 

[Andrzejek]

get the sum of the amounts as an example if the source = 'CASH'

then modify micang's statement:

select SUM(amount) As MySum
from gl_ledger 
where source = 'CASH'

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.