0

I'm creating an invoice sheet that groups each item into years, using Toad for Oracle to make my queries.

What I'm currently doing is using the same FROM and WHERE clauses as my views (as this data comes from multiple tables) 

Select DISTINCT 
SUM(Credits * Credit_Price) as "Cost", to_char(START_DATE, 'YYYY') as "YEAR", 
PERSON_ID, ITEM_TYPE
[...]
WHERE
[...]
to_char(START_DATE, 'YYYY') = '2012'

When I look at the costs in the View I'd created I see entries with a cost of 0, 100, and 0 for the appropriate person,

but if I do the SUM/GROUP BY (grouping by item_type, person_id and to_char(START_DATE, 'YYYY')) I get a cost of 3860700 for the same person!

Any ideas on how to fix this so I get 100 instead of 3860700?

EDIT: So basically I want a quantity*cost but to sum it so I get the total for that item type (instead of having multiple rows for the same item).

James
  • 87
  • 1
  • 8
  • If you remove the "DISTINCT" do you still get 3 rows: 0,100,0? Or do you get a lot more rows? – joelt Mar 18 '13 at 14:15
  • If I switch over to my View (which is identical minus the SUM/Group by statement) and remove DISTINCT I get a lot (over 2k) rows it seems... If I remove the Distinct from the SUM query I still just get the one of course. – James Mar 18 '13 at 14:18
  • 1
    Distinct is probably hiding a bug in a join - without seeing your code, it's hard to help. – Neville Kuyt Mar 18 '13 at 14:27
  • Yes - post your full query. With a result that huge the first thing that comes to mind is that you're cross-joining a table or two, as @NevilleK mentions above. – Ed Gibbs Mar 18 '13 at 14:30

1 Answers1

0

In SQL SELECT distinct sum() will not sum distinct values, so you need to first select the distinct values and then sum them up:

Select  
SUM(Credits * Credit_Price) as "Cost", to_char(START_DATE, 'YYYY') as "YEAR", 
PERSON_ID, ITEM_TYPE
[...]
 from (select DISTINCT Credits , Credit_Price, START_DATE,PERSON_ID, ITEM_TYPE
[...])
WHERE
[...]
to_char(START_DATE, 'YYYY') = '2012'

or put the distinct inside the sum like this:

Select  
SUM(DISTINCT Credits * Credit_Price) as "Cost", to_char(START_DATE, 'YYYY') as "YEAR", 
PERSON_ID, ITEM_TYPE
[...]
WHERE
[...]
to_char(START_DATE, 'YYYY') = '2012'

Note that the 2 options aren't equel, do you want to sum the distinct Credits , Credit_Price or the distinct Credits * Credit_Price ?

See sqlfiddle example

A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
  • Ahh that makes sense, the sqlfiddle example was helpful thanks! And hmm, well each row has credits & credit_price for a single transaction and I want to add up the cost for all the transactions...Can't think of which one I'd want then. – James Mar 18 '13 at 14:36
  • I seem to have figured it out with your help and doing some more testing, thanks very much! – James Mar 18 '13 at 14:40