Hello all

I know there are some SQL gurus on here and a friend of mine is trying to get a query to work but can't and I have tried to help but gave up.

The table is...

Account - Division - Hire Rev - Sales Rev - Loss Rev - Damage Rev

the table contains lots of lines (seriously lots) and each line represents one invoice.

The lines could be thus...

JOHN - A - 0.00 - 100.00 - 10.00 - 0.00
JOHN - B - 150.00 - 0.00 - 0.00 - 0.00
ADAM - A - 15.00 - 0.00 - 0.00 - 0.00
and so on...

What we would like to see as an output is this...

Account - A - B - C - D - E - ... and so on through all the divisions.
JOHN - 110.00 - 150.00 - 0.00 - 0.00 - ... etc...
ADAM - 15.00 - 0.00 - 0.00 - 0.00 - ... etc...

i.e. each line of the query will show one account and the total sum of all the revenue types for each division separately.

I hope that makes sense.

(A, B, C, D, E, ... are all divisions in this example).

Any help is appreciated.

TVM!

Table name is RI and the fields are all as I have written above but with no spaces (i.e. HireRev).

Thanks again.

Which value is used for the sum? Is there a separate table where the divisions are listed?

That makes the query a bit different, otherwise, how many divisions are you dealing with?

There is no field in the table that stores the sum of the different revenue types and it's definitely possible to make a table containing all the division if one doesn't exist and it makes this easier.

Say for example we created a table called Division with the fields Division (to match the RI table) and Name. (For simplicity).

I guess that would make it easier?

Thanks!

SELECT DISTINCT Account, Division, SUM('Hire Rev') + SUM('Sales Rev') + SUM('Loss Rev') + SUM('Damage Rev')
FROM RI
ORDER BY Account, Division
GROUP BY Account, Division


That won't create a horizontal table, but it will give you one line per Account/Division combination.

Still thinking about horizontal summing, but it isn't straight forward...

Didn't need the division table for that, but it would be a part of basic database design for such a set-up.

It seems a very strange set-up for an invoice though...

Thanks!

I'll let the guy know.



The SQL database is an export that has slowly evolved on from a CSV file into something ... err ... bigger.

It's used by the audit team where I work.

The actual working database looks nothing like this though.

Ah, I understand your first statement now.

The query you have will do this...

Account - Division - Revenue
ADAM - A - xx.xx
JOHN - A - xx.xx
JOHN - B - xx.xx

Am I right?

If so I think that would be sufficient for the purposes that the guy needs it for!

Thanks!

Yes.

^^ Wot 'e sed!