SQL Count() 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.

From stackoverflow

• 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)
  

  lordjaeger : 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 : 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.

• 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)) ...
  

• 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.