Link to home
Create AccountLog in
Avatar of hi4ppl
hi4ppl

asked on

SQL query issue

Hi I have one table where it has two kinds of record the example would be bellow:

transationtbl
account | total amount used | type of transaction
6565 | 1000 | local
6565 |200|international
5252 | 300 | local
4565 |1000| international
4565 |300| local
1020 | 100| local

so what I want a result with SQL from this is to list all accounts that have used ONLY local transaction if they used both local/international I don't need them or if they used only International ... I only need LOCAL made transaction

thanks for help
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Try this:

SELECT account, SUM([type of transaction = 'local') AS CountLocal, SUM([type of transaction = 'international') AS CountInternational
FROM YourTable
GROUP BY account
HAVING SUM([type of transaction = 'local') <> 0 AND SUM([type of transaction = 'international') =0

Open in new window

Avatar of hi4ppl
hi4ppl

ASKER

Hi this is not working it gives syntax error and im trying in postgress, plus I see you opened [ and never closed it maybe im wrong but I didn't understand this.

and when I remove [ completely I get syntax error at where it says type of transaction

thanks for help
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of hi4ppl

ASKER

Thanks I got the logic I made some modification and it worked instead of type='local' I used sum(case when transactiontype='local' then 1 else 0 end)

thanks for your time and help
Good for you working that out!  :-)

That case syntax is the best alternative since it requires only one column.  I forget it is an option... It is not available in Access (which is my primary database development platform).