SQL - Count( ) issue

Question

I have a table with a charge/credit column:

Item | PriceVal | CostVal | CHARGE_CODE
1        5             3         CH
2        8             5         CH
1       -5            -3         CR
3        7             1         CH
4       15            10         CH
1        5             3         CH

I've got the query I need to get the NET price and cost, but I'm also interested in the NET charges. Right now I have:

SELECT Item, SUM(PriceVal), SUM(CostVal)
FROM Table
GROUP BY Item

How do I get another column with the value

COUNT(SUM(CHARGE_CODE=CH)-SUM(CHARGE_CODE=CR))

I'm at a loss.

Ryan Guill · Accepted Answer · 2009-02-06 01:59:17Z

8

count() is going to count one for every value thats not null, so I don't think thats exactly what you want. Take the count out and just take the

sum(case when charge_code = CH then costval else 0 end) 
- sum(case when charge_code = 'CR' then costval else 0 end)

edited Feb 6, 2009 at 1:59

answered Feb 6, 2009 at 0:06

Ryan Guill

14k5 gold badges40 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andrew Benton Over a year ago

Yes, that's pretty much it. That last part of my question wasn't very clear. I just wanted a count, so I modified yours just a little bit: sum(case when charge_code = CH then 1 else 0 end) - sum(case when charge_code = 'CR' then 1 else 0 end) Thanks!

Ryan Guill Over a year ago

well if you want a count, you could actually do it like this: count(case when charge_code = 'CH' then 1 end) count is going to count 1 when the value is not null and 0 when it is.

paxdiablo · Accepted Answer · 2009-02-06 00:45:14Z

2

Since you have the dollar values entered as negatives in the table already, you can use the simple formula:

select
    Item,
    sum(PriceVal),
    sum(CostVal),
    sum(PriceVal-CostVal)
from Table
group by Item

I don't believe you should be subtracting the credit items as they're already negative.

If you really do want want the net count of transactions:

select
    Item,
    sum(PriceVal),
    sum(CostVal),
    sum(case when charge_code = 'CH' then 1 else 0 end) -
        sum(case when charge_code = 'CR' then -1 else 0 end)
from Table
group by Item

or, if there are only two charge codes, substitute:

sum(case when charge_code = 'CH' then 1 else -1 end)

for the last column.

edited Feb 6, 2009 at 0:45

answered Feb 6, 2009 at 0:27

paxdiablo

888k243 gold badges1.6k silver badges2k bronze badges

Comments

Draemon · Accepted Answer · 2009-02-06 00:11:01Z

0

Not 100% sure what you want, but you can count only certain rows like this:

SELECT COUNT(IF(CHARGE_CODE=CH,1,NULL)) ...

And similarly sum certain values from certain rows like this:

SELECT SUM(IF(CHARGE_CODE=CH,PriceVal,0)) ...

answered Feb 6, 2009 at 0:11

Draemon

34.9k16 gold badges80 silver badges106 bronze badges

Collectives™ on Stack Overflow

SQL - Count( ) issue

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related