0

I'm having trouble calculating a percentage from integer columns in two tables in SQLite. My employeetrained and employeetotal data are both integers, but one has commas. I'm using REPLACE to take care of that and create a new column with an alias, but I can't seem to use that alias for my calculation; I'm getting an error -- Result: no such column: trainedint Below is my data:

TRAINED table

id name employeetrained
01331 Cyberdyne Systems 41,877
18774 Nakatomi Corporation 35,411
93571 Tyrell Corporation 84,000

CORPORATIONS table

id name employeetotal
01331 Cyberdyne Systems 48872
18774 Nakatomi Corporation 94547
93571 Tyrell Corporation 247440

And my query:

CREATE TEMPORARY TABLE "trainedpercent" AS SELECT trained.id, trained.employeetrained, corporations.employees, corporations.name,
             REPLACE(employeetrained, ',', '') AS trainedint,
            ((trainedint/employees)*100.0) AS TrainedPercent
FROM trained
INNER JOIN corporations
ON trained.id = corporations.id
ORDER BY TrainedPercent
Improve this question
5
  • 1
    You can't use a derived column in the same select list. Repeat REPLACE(employeetrained, ',', '')again: ((REPLACE(employeetrained, ',', '')/employees)*100.0) AS TrainedPercent Commented Apr 28, 2021 at 16:30
  • Thanks, it allowed me to run the query and create the table, but for some reason all I'm getting in the new percent column is zeros. Commented Apr 28, 2021 at 16:45
  • Thanks @forpas - can you add as an answer so I can accept? Commented Apr 28, 2021 at 16:55
  • 1
    Change the position of 100: (100.0*REPLACE(employeetrained, ',', '')/employees*100.0) AS TrainedPercent Commented Apr 28, 2021 at 16:56
  • 1
    It's fine. This question has been asked and answered many times before. Commented Apr 28, 2021 at 16:57
Related questions
0
Find and Replace in SQLite being mucked up by percent sign
5
Compute percent in SQLite
0
Sqlite decimal value not working
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.