x404.co.uk
http://x404.co.uk/forum/

Quick SQL help
http://x404.co.uk/forum/viewtopic.php?f=3&t=4339		 Page 1 of 1
Author:  Fogmeister [ Thu Nov 19, 2009 4:59 pm ]
Post subject:  Quick SQL help
Hello all :D

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!
Author:  Fogmeister [ Thu Nov 19, 2009 5:00 pm ]
Post subject:  Re: Quick SQL help
Table name is RI and the fields are all as I have written above but with no spaces (i.e. HireRev).

Thanks again.
Author:  big_D [ Thu Nov 19, 2009 5:34 pm ]
Post subject:  Re: Quick SQL help
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?
Author:  Fogmeister [ Thu Nov 19, 2009 6:02 pm ]
Post subject:  Re: Quick SQL help
big_D wrote:
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!
Author:  big_D [ Thu Nov 19, 2009 6:54 pm ]
Post subject:  Re: Quick SQL help
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... :?
Author:  Fogmeister [ Thu Nov 19, 2009 7:56 pm ]
Post subject:  Re: Quick SQL help
Thanks!

I'll let the guy know.

big_D wrote:
It seems a very strange set-up for an invoice though... :?


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.
Author:  Fogmeister [ Thu Nov 19, 2009 7:59 pm ]
Post subject:  Re: Quick SQL help
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!
Author:  EddArmitage [ Thu Nov 19, 2009 8:00 pm ]
Post subject:  Re: Quick SQL help
Fogmeister wrote:
Account - Division - Revenue
ADAM - A - xx.xx
JOHN - A - xx.xx
JOHN - B - xx.xx

Am I right?

Yes.
Author:  big_D [ Thu Nov 19, 2009 8:24 pm ]
Post subject:  Re: Quick SQL help
^^ Wot 'e sed! ;)
Page 1 of 1 All times are UTC
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/