[Solved] Adding two fields

Creating tables and queries
Post Reply
crushedoreos
Posts: 3
Joined: Tue Feb 23, 2021 6:22 pm

[Solved] Adding two fields

Post by crushedoreos »

I have a table and I want to create a query which will add the ITEM_QTY fields which correspond to VND_02, in other words I need the query to return the maximum number of items in VND_02's possession.
ITEM_DETAILS
ITEM_DETAILS
1xFoR0t.png (9.98 KiB) Viewed 2788 times
https://imgur.com/a/Hq8PgQi

Any help highly appreciated :)
Last edited by crushedoreos on Tue Feb 23, 2021 8:01 pm, edited 2 times in total.
OpenOffice 4.1.9 WINDOWS 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: ADDING TWO FIELDS

Post by FJCC »

If you want the sum of items that VND_02 has, try

Code: Select all

SELECT "VENDOR_ID", SUM("ITEM_QTY") AS "Total_Items" FROM "MyTable" WHERE "VENDOR_ID" = 'VND02' GROUP BY "VENDOR_ID"
You can get the sum of items for each vendor with

Code: Select all

SELECT "VENDOR_ID", SUM("ITEM_QTY") AS "Total_Items" FROM "MyTable"  GROUP BY "VENDOR_ID"
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
crushedoreos
Posts: 3
Joined: Tue Feb 23, 2021 6:22 pm

Re: ADDING TWO FIELDS

Post by crushedoreos »

FJCC wrote:If you want the sum of items that VND_02 has, try

Code: Select all

SELECT "VENDOR_ID", SUM("ITEM_QTY") AS "Total_Items" FROM "MyTable" WHERE "VENDOR_ID" = 'VND02' GROUP BY "VENDOR_ID"
I tried this, but I keep getting an error message.
SQL Status: 37000
Error code: -15

Set Function on non-numeric data is not allowed in statement [SELECT "VENDOR_ID", SUM( "ITEM_QTY" ) AS "Total_Items" FROM "ITEM_DETAILS" WHERE "VENDOR_ID" = 'VND02' GROUP BY "VENDOR_ID"]
The SQL command leading to this error is:

SELECT "VENDOR_ID", SUM( "ITEM_QTY" ) AS "Total_Items" FROM "ITEM_DETAILS" WHERE "VENDOR_ID" = 'VND02' GROUP BY "VENDOR_ID"
OpenOffice 4.1.9 WINDOWS 10
crushedoreos
Posts: 3
Joined: Tue Feb 23, 2021 6:22 pm

Re: ADDING TWO FIELDS

Post by crushedoreos »

Sorry, please ignore my last post. Changed data type to numeric for the fields in question by editing my table, and now I have the desired output.
Thank you so much for your help.
OpenOffice 4.1.9 WINDOWS 10
Post Reply