[Solved] Adding two fields

Creating tables and queries

[Solved] Adding two fields

Postby crushedoreos » Tue Feb 23, 2021 6:51 pm

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.
1xFoR0t.png
ITEM_DETAILS
1xFoR0t.png (9.98 KiB) Viewed 192 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
crushedoreos
 
Posts: 3
Joined: Tue Feb 23, 2021 6:22 pm

Re: ADDING TWO FIELDS

Postby FJCC » Tue Feb 23, 2021 7:37 pm

If you want the sum of items that VND_02 has, try
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
SELECT "VENDOR_ID", SUM("ITEM_QTY") AS "Total_Items" FROM "MyTable"  GROUP BY "VENDOR_ID"
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7970
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: ADDING TWO FIELDS

Postby crushedoreos » Tue Feb 23, 2021 7:55 pm

FJCC wrote:If you want the sum of items that VND_02 has, try
Code: Select all   Expand viewCollapse view
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

Postby crushedoreos » Tue Feb 23, 2021 8:00 pm

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
crushedoreos
 
Posts: 3
Joined: Tue Feb 23, 2021 6:22 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 4 guests