• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

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
0
hi4ppl
Asked:
hi4ppl
  • 3
  • 2
1 Solution
 
mbizupCommented:
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

0
 
hi4pplAuthor Commented:
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
0
 
mbizupCommented:
Yep - that was a typo.  Did you try closing the brackets?

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



If field names have spaces in them, you need to enclose them in [] brackets (Access and SQL Server)... not sure about postgress, but that also most likely needs some kind of delimiter around any field name containing spaces.
0
 
hi4pplAuthor Commented:
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
0
 
mbizupCommented:
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).
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now