CODEWITHSUNDEEP
sqlite
user121
user121

Reputation: 931

How can I subtract values within a single column of SQLite table?

My original table in sqlite is as follows:

--------------------
names_ID  | values
--------------------
frank     | 31
chris     | 50
chris     | 40
tim       | 65
tim       | 85

Then I apply the following query:

SELECT names_ID, GROUP_CONCAT(values, ',') FROM table GROUP BY names_ID

From the above query I get the following table:

--------------------
names_ID  | values
--------------------
frank     | 31
chris     | 50,40
tim       | 65,85

For rows where there are two values separated by a comma, I would like to get the percentage of these two values (for example, for the 'tim' row, the percentage would be 65/85 *100 = 76.5).

I would like my expected result to be:

--------------------
names_ID  | percent
--------------------
chris     | 80
tim       | 76.5

I have been unable to come up with an attempt that comes anywhere close to my desired output. Any help appreciated.

Upvotes: 0

Views: 863

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

You don't need to go through CSV to answer your question I think. If each name really only has two values (at most) associated with it, then a simple GROUP BY aggregation should work:

SELECT
    names_ID,
    CAST(MIN(values) AS float) / CAST(MAX(values) AS float) * 100 AS percent
FROM yourTable
GROUP BY names_ID
HAVING COUNT(*) = 2

Note: I don't like the idea of grouping by names_ID, because first names may not be unique across even a small group of people. Rather, names_ID should probably be an auto increment column, and you should create a new column called names which contains the actual name text.

Upvotes: 1

Related Questions