SQL Query Help!!

[Scrappy666]

Hi Everyone,

Sorry if im posting this in the wrong place! Couldn't see a SQL section
(If anyone can help with SQL at all)


I've ran a query from the accountancy system we use, although the records produced will soon exceed the 1m rows excel allows
I need to filter this query so that only negative values appear

My issue is each booking (I work at a travel agents) is split across multiple records

My idea is to add an additional column at the end that could look something like a sumif
=sumif('booking reference', 'reference' ,'amount')
Which i can then filter on for negative values

If anyone has any idea how to do this it would be greatly appriciated!!!
Thanks in advance!
Dan

 

[Fazza]

Hi, Dan

Some simple data might help?

If you wanted simply to exclude all records with negative values, and I think this is not what you're asking, use something like

SELECT reference, amount
FROM table
WHERE amount < 0

If however you want only negative sums, try

SELECT reference, amount
FROM table
GROUP BY reference
HAVING SUM(amount) < 0

Untested.

If you want something different, or those don't work, please advise sample input & output data.

regards

 

[Scrappy666]

Thanks for the reply!

You've pretty much hit the nail on the head with regards to what i need
I've put in what you have suggested but i'm received the below error

"Amount is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

A sample of data is below also:

Reference Amount
123456/1 100.00
123456/1 200.00
123456/2 200.00

OUTPUT:

123456/1 300.00
123456/2 200.00



I know thats not the best layout in the world but i hope you can understand it

 

[Scrappy666]

Oh!!!
I think i just made a little progress

If i set the amount column as a number in the SELECT section it seems to give me a result!
The only issue i would have now is that not all my columns are numbers, is there a way to tell SQL my text columns will all have the same data and to just take one value??

Again, example below

Reference Amount Name
123456/1 100.00 Smith
123456/1 200.00 Smith
123456/2 200.00 Brown

OUTPUT:

123456/1 300.00 Smith
123456/2 200.00 Brown


Thank you !

 

[Kyle123]

Try: SELECT reference, sum(amount)
FROM table
GROUP BY reference
HAVING SUM(amount) < 0

 

[Scrappy666]

Thanks Kyle,

I also have columsn that include text
Do you know which function i would need to allow text columns to work in this way?

I've put an example in the above post