SQL SUM Query - Mind has drawn a blank! 1

[BiggerBrother]

I have 2 tables, one for transactions and the other for departments.

Transactions:
trans_id
trans_amount
trans_date
trans_dept

Department:
dep_id
dep_name

I need to return a recordset with 4 fields, which are:
dep_id
dep_name
sum(transactions for this dep)
sum(transactions for this dep WHERE trans_date is not null)

I'm not sure how to achieve the two sum columns, whether I can use the sum() function, or it has to be a join?

Any help would be great.

Many thanks

BB

 

[dalchri]

I would do this:

SELECT
   trans_dept, 
   dep_name,
   SUM(trans_amount),
   SUM(IF(trans_date Is Null, 0, trans_amount))
FROM Transactions
LEFT JOIN Department
ON Transactions.trans_dept = Department.dep_id
GROUP BY
   trans_dept,
   dep_name

You may need to add trans_date as a group by field.

 

[r937]

dalchri, i think your LEFT OUTER join should be in the other direction

... Department LEFT JOIN Transactions

alternatively, you could leave them in the order you've specified but write

... Transactions RIGHT JOIN Department

the reason being that it's easy to imagine a department with no transactions, however, it should be impossible to have a transaction without a department

in the same regard, you should use dep_id in the SELECT and GROUP BY, not trans_dept, which will be null if the department has no transactions





rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[dalchri]

>>the reason being that it's easy to imagine a department with no transactions, however, it should be impossible to have a transaction without a department


Not in our data

 

[r937]

ah, i see

you guys don't believe in relation integrity then?

i mean, aside from the fact that you have to do it yourself and cannot trust your "relational database" to do it?

;-)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[BiggerBrother]

Fantastic. Just what I needed. Took a little whuile to work out why the LEFT JOIN works, but now I've learnt something new.

Great, and thanks again

BB