How can I perform a GROUP BY in *SQL* when the group_name values are similar but not exactly the same? In my dataset, the group_name values may differ slightly (e.g., "Apple Inc.", "AAPL", "Apple"), but conceptually they refer to the same entity. The similarity might not be obvious or consistent, so I might need to define a custom rule or function like is_similar() to cluster them. For simple cases, I can extract a common pattern using regex or string functions (e.g., strip suffixes, lowercase, take prefixes). But how should I handle more complex scenarios, like fuzzy or semantic similarity? Case: group_name | val ---------------|----- 'Apple Inc.' | 100 'AAPL' | 50 'Apple' | 30 'Microsoft' | 80 'MSFT' | 70 What I want to achieve: new_group_name | total_val ----------------|---------- 'Apple' | 180 'Microsoft' | 150 What are the best approaches to achieve this in *SQL*? And how would I write a query like this: SELECT some_characteristic(group_name) AS new_group_name, SUM(val) FROM tb1 GROUP BY new_group_name;

WITHOUT ROWID is an sqlite optimization. Doc states, it is making the primary key of the table to a clustered index. As far I know, clustered index means that the physical ordering of the rows will follow the index, making linear scans effective on it. However, what will happen if I insert a row into the middle of such a table? Will it be really needed to shift the whole table file after that?

Sqlite, contrary most SQL-compatible DB engine, is working directly on files instead over network sockets. It is also not very good in concurrency, beside that deletion operators are mostly advisory (i.e. do not free the places of the deleted entities). That results that an sqlite database needs regularly be VACUUM-ed, at least those with regular modify or delete operations. Vacuum works by rebuilding the database file, without the free spaces in it. Needless to say, it is absolutely non-concurrent with anything, making it to a costly operation - we have downtime for the vacuum. *However, I think there should exist some indicator which compares the size of the database file to the size of the actual data in it.* Using this indicator, it could be said, do we need a vacuum-ing or not. Does it exist?

In sqlite I have the following table

CREATE table IF NOT EXISTS redirect (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            url_from TEXT not null,
            url_to TEXT not null,
            method TEXT not null,
            http_status_code INTEGER not null CHECK( http_status_code IN (300,301,302,304,304,308,307) ) DEFAULT 301,
            use_in_https  INTEGER not null CHECK(use_in_https IN (0,1)) DEFAULT 0,
            use_in_http  INTEGER not null CHECK(use_in_http IN (0,1)) DEFAULT 1,
            exact_match INTEGER not null CHECK(exact_match IN (0,1)) DEFAULT 1
        );

And I insert the following values:

INSERT INTO redirect (url_from,url_to,method,http_status_code,use_in_http,use_in_https) VALUES
            ('http://google.com/mytest ','http://yahoo.com ','GET',301,1,1),
            ('http://google.com/mytest?param=hello ','http://ikariam.gr ','GET',301,1,1),
            ('http://google.com/mytest ','http://yandex.com ','GET',302,1,0)

In my app I search based on an incomming url and I want to find the best match: If the url is http://google.com/mytest I want the following result to be returned: url_from | url_to | method | http_status_code | use_in_http | use_in_http --- | --- | --- | --- | --- | --- | http://google.com/mytest ' | 'http://yahoo.com ' |'GET' | 301 | 1 | 1 But once I either for: * http://google.com/mytest/lorem_uipsus * http://google.com/mytest?param=petralka * http://google.com/mytest?param33=petralka I want this result to be returned: url_from | url_to | method | http_status_code | use_in_http | use_in_https --- | --- | --- | --- | --- | --- | http://google.com/mytest ' | 'http://yandex.com ' |'GET' | 302 | 1 | 0 As you can see request_from may contain a subscript for the provided search term. Therefore, I thought the query should be:

select 
  url_from,url_to,method,http_status_code,use_in_http,use_in_https
from
  redirect
where
  -- Here search by substring
  -- url_from must be a substring of a provided search term
order by exact_match DESC
LIMIT 1

But here I am stuck, how I can check that a column contains a substring of a provided searchterm? What I want is to do the opposite of:

url_from like '%input%

Meaning:

'input' like url_from

Any ideas?

If I have multiple database readers, which are basically instances of Sqlite that open the db file in "read" mode, is it normal that for a single writer that opens the db in "create" mode to not be able to release a transaction until all readers close the database? I'm trying to figure out if it's normal behaviour of sqlite, or it's bug/limitation of a specific sqlite library I'm using. I don't understand why would a writer need to wait for readers to stop using the database file, doesn't make sense to me, because readers cannot alter data

I'm trying to create generic upsert query builder with specific behavior. Table has two unique constraints: primary key _itId_ and column _slug_:

create table it (
    itId integer primary key -- an alias for rowid
  , slug text
  , text text
  
  , CONSTRAINT "itId" UNIQUE("itId")
  , CONSTRAINT "slug" UNIQUE("slug")
);

-- fill the table:
insert into it
    (itId, slug, text)
values
    (null, 'a', 'letter a')
  , (null, 'b', 'letter b')
  , (null, 'c', 'letter c')
;

| itId | slug | text | | ---- | ---- | -------- | | 1 | a | letter a | | 2 | b | letter b | | 3 | c | letter c | Then, here's an attempt of batch upsert of rows using different keys:

insert into it
    (itId, slug, text)
values
    (null, 'a', 'latin letter a') -- update text by slug
  , (   2, 'β', 'greek letter beta') -- update slug and text by primary key
  , (   9, 'c', 'latin letter c') -- update primary key and text by slug
on conflict (itId) do update set
    text = coalesce(excluded.text, text)
  , itId = itId -- save current value for autoincremented column
  , slug = coalesce(excluded.slug, slug)
on conflict (slug) do update set
    text = coalesce(excluded.text, text)
  , itId = coalesce(excluded.itId, itId) -- here's a trouble!

(I'm using [SQLite 3.35](https://www.sqlite.org/releaselog/3_35_4.html) , which allows multiple on conflict clauses) | itId | slug | text | | ---- | ---- | ----------------- | | 2 | β | greek letter beta | | **4**| a | latin letter a | | 9 | c | latin letter c | As you can see, b changed to β -- that's okay; c has changed its _itId_ according to upserted value 3 -> 9. But there's a trouble with a (was matched by slug): _excluded.itId_ resolves with a value 4 instead of null within second on conflict clause. It seems that the value of _itId_ for row a is replaced with the next available autoincrement value for column _itId_ (an alias of _rowid_). Expectation: itId = coalesce(excluded.itId, itId) => coalesce(null, 1) => 1: | itId | slug | text | | ---- | ---- | ----------------- | | **1**| a | latin letter a | | 2 | β | greek letter beta | | 9 | c | latin letter c | Is there a way to achieve such a result using insert ... on conflict do update for all of these cases? [Online playground](https://sqlime.org/#deta:rwfs8hlesabw)

I'm having trouble with a self-referencing foreign key in SQLite. I've created a table to store employee details, where each employee can have a manager who is also an employee. Here's the table definition:

CREATE TABLE Employees (
    Employee_ID INTEGER PRIMARY KEY,
    Name TEXT,
    Manager_ID INTEGER,
    FOREIGN KEY (Manager_ID) REFERENCES Employees(Employee_ID)
);

I've enabled foreign key constraints with PRAGMA foreign_keys = ON. However, when I insert data with a non-existent Manager_ID, SQLite does not raise an error. Here are the steps I've taken: 1. Enabled foreign key constraints:

PRAGMA foreign_keys = ON;

2. Created the table:

CREATE TABLE Employees (
    Employee_ID INTEGER PRIMARY KEY,
    Name TEXT,
    Manager_ID INTEGER,
    FOREIGN KEY (Manager_ID) REFERENCES Employees(Employee_ID)
);

3. Inserted data:

INSERT INTO Employees (Employee_ID, Name, Manager_ID) VALUES (1, 'Fitch', NULL);
INSERT INTO Employees (Employee_ID, Name, Manager_ID) VALUES (2, 'Alley', 1);

// This should fail because 9 doesn't exist, but doesn't:
INSERT INTO Employees (Employee_ID, Name, Manager_ID) VALUES (6, 'Blake', 9);

Despite these steps, SQLite does not enforce the foreign key constraint for Manager_ID.

SELECT * FROM Employees

Gives: | Employee_ID | Name | Manager_ID | |-------------|-------|------------| | 1 | Fitch | NULL | | 2 | Alley | 1 | | 6 | Blake | 9 | **Are Self-Referencing Foreign Keys supported in SQLite? If so, how do I get it to work?**

I have an SQLite table containing a "last changed" column with a date in the format e.g. "2022-11-07T11:51:06+01:00". Coreutils' date outputs this by using the following command: date +%FT%T%:z I can almost generate it from inside SQLite by using SELECT STRFTIME('%Y-%m-%dT%H:%M:%S', DATETIME('now', 'localtime')); however, this lacks the timezone, and as far as I grasp the docs, there's no timezone placeholder. So: Can I get the current local timezone using SQLite functions?

I have two tables, each with a round and an id column and additional data columns. One table is the planned version, the other one is the actual one. E.g.: planned: round | id | value ------+----+------ 1 | 1 | a ------+----+------ 2 | 1 | d ------+----+------ 2 | 2 | e actual: round | id | value ------+----+------ 1 | 1 | a ------+----+------ 1 | 2 | b ------+----+------ 2 | 1 | c Now I want to select all data, but if such a dataset (identified by round and id) is present both in the planned and in the actual table, I want to use the actual value. Additionally, all planned values not present in the actual table should be selected. In this example, I'd like to get: round | id | value ------+----+------ 1 | 1 | a (present in both, but identical) ------+----+------ 1 | 2 | b ("actual" values, not present in "planned") ------+----+------ 2 | 1 | c ("actual" values, different from "planned") ------+----+------ 2 | 2 | e ("planned" values, not present in "actual") Edit: I found a way to select this using two UNIONed queries, one with a subquery: SELECT round, id, value FROM actual UNION SELECT round, id, value FROM planned WHERE NOT EXISTS ( SELECT TRUE FROM actual WHERE actual.round = planned.round AND actual.id = planned.id ) ORDER BY round, id but this seems to be a bit clumsy … is there a more elegant way?

I have two tables: jobs CREATE TABLE IF NOT EXISTS jobs ( job_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, job_name TEXT, prime_desc INTEGER NOT NULL REFERENCES descriptions(desc_id), scnd_desc INTEGER NOT NULL REFERENCES descriptions(desc_id), ); INSERT INTO jobs (job_name, prime_desc, scnd_desc) VALUES ('Soldier', 1, 5); INSERT INTO jobs (job_name, prime_desc, scnd_desc) VALUES ('Pastor', 2, 3); INSERT INTO jobs (job_name, prime_desc, scnd_desc) VALUES ('Firefighter', 5, 4); descriptions CREATE TABLE IF NOT EXISTS descriptions ( desc_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, desc_name TEXT, ); INSERT INTO descriptions (desc_name) VALUES ('Strong'); INSERT INTO descriptions (desc_name) VALUES ('Pious'); INSERT INTO descriptions (desc_name) VALUES ('Honest'); INSERT INTO descriptions (desc_name) VALUES ('Agile'); INSERT INTO descriptions (desc_name) VALUES ('Brave'); In this example, I would like to be able to add two descriptions to a job. The code works and I can query it: SELECT * FROM jobs; This returns the expected result: 1|Soldier|1|5 2|Pastor|2|3 3|Firefighter|5|4 What I have been trying to do is get a query that lists the job name and the primary and secondary description names: Firefighter|Brave|Agile I have not been able to get that result. I have tried: SELECT jobs.job_name, desc.desc_name FROM jobs JOIN descriptions ON jobs.prime_desc = descriptions.desc_id JOIN descriptions ON jobs.scnd_desc = descriptions.desc_id; This returns an error: Parse error: ambiguous column name: descriptions.desc_name SELECT jobs.job_name, descriptions.desc_name FROM jobs JOIN descriptions ON... error here ---^ I searched the Internet on that error and couldn't find anything that is an example of what I'm trying to accomplish. I tried many different combinations of: RIGHT JOIN, LEFT JOIN, UNION, UNION ALL, ALIAS in multiple configurations. The closest I came is with UNION, but it did not output the results as desired; here's the UNION I tried: SELECT jobs.job_name, descriptions.desc_name FROM jobs JOIN descriptions ON jobs.prime_desc = descriptions.desc_id UNION SELECT jobs.job_name, descriptions.desc_name FROM jobs JOIN descriptions ON jobs.scnd_desc = descriptions.desc_id; This returns: Soldier|Strong Pastor|Pious Firefighter|Brave Soldier|Brave Pastor|Honest Firefighter|Agile It's returning the results as asked, so I know it's something I'm doing wrong. I just don't know what it is that I'm doing that won't print the results in one line per job, as illustrated by the Firefighter example above. I've tried creating a column alias, a table alias, et al. I'm at a loss as to how to approach this. Any help is appreciated.

I'm working with, I think, the lastest version of Sqlite and C++. I have a table with data, and I need to return this data in the order I inserted it. Do I have any guarantee that the columns will always be returned in this order? Maybe I must add another column to set the order and use ORDER BY:

I have a table Product with two columns: name of product and price

CREATE TABLE IF NOT EXISTS YearCounters (
    year INTEGER PRIMARY KEY,
    counter INTEGER DEFAULT 0
);

-- Product Table
CREATE TABLE IF NOT EXISTS Products (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL
);

My objectif,Creates a unique key in the Product table, which must be composed of the year plus a number of size 10 which increments for each new product added YEAR+COUNT(10) :

INSERT INTO Products (name, price) VALUES ('Produit B', 20.15);
INSERT INTO Products (name, price) VALUES ('Produit C', 23.75);

The product table should look like this Example:

SELECT * FROM Products;

2024000000000001|Produit B|20.15
2024000000000002|Produit C|23.75

I've developed a TRIGGER for the primary key, but it doesn't work. .

CREATE TRIGGER IF NOT EXISTS increment_counter_and_set_product_id
BEFORE INSERT ON Products
FOR EACH ROW
BEGIN
    -- 
    UPDATE YearCounters
    SET counter = counter + 1
    WHERE year = strftime('%Y', 'now');

    --
    INSERT OR IGNORE INTO YearCounters (year, counter)
    VALUES (strftime('%Y', 'now'), 0);

    --
    SELECT counter
    INTO temp_counter
    FROM YearCounters
    WHERE year = strftime('%Y', 'now');

    -- 
    SET NEW.id = strftime('%Y', 'now') || printf('%010d', temp_counter);
END;

Error: I recieve the error

Parse error near line 15: near "INTO": syntax error
SELECT counter  INTO temp_counter  FROM Y
   error here ---^

What am I doing wrong?

Obligatory I'm a beginner at database management. To be specific about my situation, I'm working in SQLite3 in Python as a means to an end for now for a finance project. The table is called transactions and it stores a market_id, price, and quantity, where each price-quantity pair is a tick. Inserting ticks into the table works perfectly, querying specific data feels nearly impossible for what I'm trying to do. What I'm trying to do: - Find all prices and quantities in a market by market_id - Partition prices and quantities by the floor division of their respective row ID by a variable amount - Find the first, highest, lowest, and last price and the sum of all quantities in the partitions - Return the last n queried partitions To quickly summarize, get the latest OHLCV information in tick bars. What I've tried so far: Attempt 1:

SELECT
  (SELECT price WHERE group_id = 1),
   MAX(price) AS high,
   MIN(price) AS low,
  (SELECT price WHERE group_id = 0),
   SUM(quantity) AS volume
FROM (
   SELECT
     price,
     quantity,
     ROWID as id,
     (ROWID % ?) as group_id,
     CAST((ROWID - 1) / ? AS INTEGER) AS group_partition
     FROM transactions
     WHERE market_id = ?
      ORDER BY id DESC
   )
GROUP BY
  group_partition
ORDER BY
  id DESC
LIMIT ?

Seemed promising but gave null values so likely isn't good code. Attempt 2:

SELECT
  FIRST_VALUE(price) OVER (PARTITION BY group_id) AS open,
  MAX(price) AS high,
  MIN(price) AS low,
  LAST_VALUE(price) OVER (PARTITION BY group_id) AS close,
  SUM(quantity) AS volume,
  MIN(id) AS min_id
FROM (
  SELECT
    price,
    quantity,
    ROWID as id,
    CAST((ROWID - 1) / ? AS INTEGER) AS group_id
    FROM transactions
    WHERE market_id = ?
    ORDER BY id ASC
)
GROUP BY group_id
ORDER BY min_id DESC
LIMIT ?

Gave incorrect open and close prices when I investigated further. For instance, if I had a partition of prices as an array [100.12, 99.96, 99.98, 100.05, 99.9] in that order I would get [100.12, 100.12, 99.9, 100.12] as the open, high, low, and close, where the close was just the same as the open. And many other attempts. To put it simply, I've been working on this for over 3 hours and can't figure it out, so any help or suggestions is greatly appreciated. Edit for correct query and output as explained by Andrea B.:

SELECT
    open,
    MAX(price) AS high,
    MIN(price) AS low,
    close,
    SUM(quantity) AS volume,
    MIN(id) AS min_id
FROM (
    SELECT
        price,
        quantity,
        ROWID as id,
        (ROWID - 1) / ? AS group_id,
        FIRST_VALUE(price) OVER (PARTITION BY (ROWID - 1) / ? ORDER BY ROWID) AS open,
        FIRST_VALUE(price) OVER (PARTITION BY (ROWID - 1) / ? ORDER BY ROWID DESC) AS close
        FROM transactions
        WHERE market_id = ?
        ORDER BY id DESC
)
GROUP BY group_id
ORDER BY min_id DESC
LIMIT ?

And it's output Data Array (1 partition): [99.76, 99.76, 99.71, 99.79, 99.87] Output: [99.76, 99.87, 99.71, 99.87]

I have a table of videos with an id, skill_level and duration column and I am attempting to write a query that will return a playlist of distinct videos that has a total duration exceeding X.

select _id, duration, running_total
from (select *, sum(duration) over (order by RANDOM()) as running_total
      from videos
      where skill_level in ('beginner', 'superbeginner') AND watched IS NULL
     )
where running_total <= 7200;

This works and returns the following:

5e4d11fbaac87f3820954c45|305|305
64e51ad279223a9ad03525c2|930|1235
5e4d1204aac87f3820954c64|627|1862
5e4d1212aac87f3820954c95|367|2229
641e05c2d7c1512d278dc51a|361|2590
5e4d1200aac87f3820954c57|444|3034
649aa7089776ba12d8de28d5|329|3363
5e4d123eaac87f3820954d2c|573|3936
5e4d1216aac87f3820954ca1|399|4335
5e4d120daac87f3820954c84|315|4650
5e4d11dfaac87f3820954be1|510|5160
64f7036d3d578af923ba2afc|326|5486
5e4d11fcaac87f3820954c47|437|5923
5fa51afc27a912152cc51c33|1062|6985

But ideally I want to return enough records to exceed X but only by one. 6375274c1cff4a7e31b04a0d below puts us over 7200.

5e4d11fbaac87f3820954c45|305|305
64e51ad279223a9ad03525c2|930|1235
5e4d1204aac87f3820954c64|627|1862
5e4d1212aac87f3820954c95|367|2229
641e05c2d7c1512d278dc51a|361|2590
5e4d1200aac87f3820954c57|444|3034
649aa7089776ba12d8de28d5|329|3363
5e4d123eaac87f3820954d2c|573|3936
5e4d1216aac87f3820954ca1|399|4335
5e4d120daac87f3820954c84|315|4650
5e4d11dfaac87f3820954be1|510|5160
64f7036d3d578af923ba2afc|326|5486
5e4d11fcaac87f3820954c47|437|5923
5fa51afc27a912152cc51c33|1062|6985
6375274c1cff4a7e31b04a0d|1138|8123

Is it possible to write a query to achieve this in sqlite?

#user_input.py from flask import Flask, request, jsonify import sqlite3 app = Flask(__name__) def process_user_input(data): # Retrieve user input from the data stock_name = data.get('stockName') # Create a new connection and cursor conn = sqlite3.connect('C:/SQLite/pythonstock/database.db') c = conn.cursor() c.execute("SELECT price FROM stocks WHERE lower(stockName) = ?", (stock_name.lower(),)) # Fetch the result row = c.fetchone() if row: price = row # Access the first column (price) # Perform further operations with the retrieved price else: # Handle the case when no row is found for the given stock_name price = None # or any appropriate default value or error handling # Close the cursor and connection c.close() conn.close() processed_input = price # Create a JSON-serializable response response = {'processed_input': processed_input} return response def process_user_input1(data): print("Process_user_input1 started") # Retrieve user input from the data stock_name = data.get('stockName') BRstage=data.get('BRstage') # Create a new connection and cursor conn = sqlite3.connect('C:/SQLite/pythonstock/database.db') c = conn.cursor() c.execute('update stocks set BRstage=BRstage where stockName=stock_name') conn.commit() conn.close() processed_input1='success' response = {'processed_input1': processed_input1} return response Traceback (most recent call last): File "C:\Python312\Lib\site-packages\flask\app.py", line 1455, in wsgi_app response = self.full_dispatch_request() ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Python312\Lib\site-packages\flask\app.py", line 869, in full_dispatch_request rv = self.handle_user_exception(e) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Python312\Lib\site-packages\flask_cors\extension.py", line 176, in wrapped_function return cors_after_request(app.make_response(f(*args, **kwargs))) ^^^^^^^^^^^^^^^^^^ File "C:\Python312\Lib\site-packages\flask\app.py", line 867, in full_dispatch_request rv = self.dispatch_request() ^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Python312\Lib\site-packages\flask\app.py", line 852, in dispatch_request return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "c:\Users\Steven\stock.investing.decision.making\my-app\flask-app\main.py", line 25, in process_user_input_route1 result = process_user_input1(data) ^^^^^^^^^^^^^^^^^^^^^^^^^ File "c:\Users\Steven\stock.investing.decision.making\my-app\flask-app\user_input.py", line 57, in process_user_input1 c.execute('UPDATE stocks SET BRstage = ? WHERE lower(stockName) = ?', (BRstage, stock_name.lower())) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ sqlite3.OperationalError: no such column: stock_name 127.0.0.1 - - [06/Nov/2023 22:11:41] "POST /process_user_input1 HTTP/1.1" 500 - File "c:\Users\Steven\stock.investing.decision.making\my-app\flask-app\user_input.py", line 57, in process_user_input1 c.execute('UPDATE stocks SET BRstage = ? WHERE lower(stockName) = ?', (BRstage, stock_name.lower())) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ sqlite3.OperationalError: no such column: stock_name 127.0.0.1 - - [06/Nov/2023 23:21:36] "POST /process_user_input1 HTTP/1.1" 500 -

I need to build a multi-tenant solution with a requirement for strict data isolation (meaning isolated Databases at tenant level). Even the authentication is different for each tenant? I find that SQLite with all its compromises to be a right solution. But I understand the maintenance perils of having dedicated instance for each application and the costs it brings. So, I intend to have a multi-tenant single instance solution for the system where all users will use the same instance on the same domain (separated by url-paths). The database connection will be kept in memory for using LRU cache. That brings me to my question: - Will this frequent opening of SQLite file cause considerable penalty if load increases moderately? Are there any benchmarks I can refer to? - Will this corrupt my DB files for any unforeseeable reasons I am not taking into account? > *Note: My backend will be either Node.js or Elixir/OTP* considering that application is IO bound CPU bound.

Based on Sqlite docs: https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison , especially this statement: > If one operand has INTEGER, REAL or NUMERIC affinity and the other operand has TEXT or BLOB or no affinity then NUMERIC affinity is applied to other operand. I would expect the following query:

CREATE TABLE invoice (
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  amount DOUBLE PRECISION DEFAULT NULL
);

insert into invoice (amount) values (4.0);
insert into invoice (amount) values (15.0);
insert into invoice (amount) values (4.0);

select *,
    typeof(amount), amount = '4',
    typeof(sum(amount)), sum(amount) = '4', sum(amount) = '4.0', sum(amount) = 4
from invoice
group by id;

to return the same result for sum(amount) = '4' as for amount = '4' for each row as both operand types have the same type in each comparison (verified using typeof(), for non-SUM() the comparison is working as expected). Demo: http://sqlfiddle.com/#!5/59238/2

Basically the next step for https://dba.stackexchange.com/q/330357/272354 : I created a trigger after insert to set SEQ to the highest SEQ plus one if it's less than one. Basically: ~~~lang-sqlite3 CREATE TRIGGER TRG_NAMES_ADD2 AFTER INSERT ON NAMES FOR EACH ROW WHEN NEW.SEQ < 1 BEGIN UPDATE NAMES SET SEQ = (SELECT IFNULL(MAX(SEQ), 0) + 1 FROM NAMES WHERE TYPE = NEW.TYPE) WHERE TYPE = NEW.TYPE AND SEQ = NEW.SEQ; END; ~~~ However I'd like to do the same thing for TYPE independently from SEQ. The problems I found were that I can have only one UPDATE per trigger, and the AFTER INSERT triggers may be executed in any order. I tried a second trigger like this: ~~~lang-sqlite3 CREATE TRIGGER TRG_NAMES_ADD1 AFTER INSERT ON NAMES FOR EACH ROW WHEN NEW.TYPE < 1 BEGIN UPDATE NAMES SET TYPE = (SELECT IFNULL(MAX(TYPE), 0) + 1 FROM NAMES), SEQ = 1 WHERE TYPE = NEW.TYPE AND SEQ = NEW.SEQ; END; ~~~ However for completeness TRG_NAMES_ADD2 should run after TRG_NAMES_ADD1, or I would have to put both actions (set TYPE to a possibly new value (if < 1), and then use that new value to possibly set a new value for SEQ (if < 1), too) into one trigger. Unfortunately that's beyond my capabilities. For convenience, here's the table creation and some sample inserts: ~~~lang-sqlite3 CREATE TABLE NAMES ( TYPE INTEGER NOT NULL, SEQ INTEGER NOT NULL, NAME VARCHAR(20), PRIMARY KEY (TYPE, SEQ) ); insert into NAMES (type, seq, name) values (2, 3, "T1"); insert into NAMES (type, seq, name) values (2, 0, "T2"); insert into NAMES (type, seq, name) values (2, 0, "T3"); insert into NAMES (type, seq, name) values (0, 1, "T4"); insert into NAMES (type, seq, name) values (0, 0, "T5"); ~~~ So the assignments should be like this: ~~~lang-text T|S|N ----- 2|3|T1 # T,S set manually 2|4|T2 # S set automatically 2|5|T3 # S set automatically 3|1|T4 # T set automatically 4|1|T5 # T,S set automatically ~~~

With littele SQL(ite) practice, I tried to create a table with a composite primary key: ~~~lang-sqlite3 CREATE TABLE NAMES ( TYPE INTEGER NOT NULL, SEQ INTEGER NOT NULL, NAME VARCHAR(20), PRIMARY KEY (TYPE, SEQ) ); ~~~ The idea is to store associations from numbers to names, i.e.: SEQ to NAME and back. As there is a variable number of such "classes" I added a TYPE column, that is that the SEQ numbers (and NAMEs) should be *unique within a specific TYPE*. I also tried to create a trigger that will automatically assign the "next free" SEQ number within a specific TYPE like this: ~~~lang-sqlite3 CREATE TRIGGER TRG_NAMES_ADD AFTER INSERT ON NAMES FOR EACH ROW BEGIN UPDATE NAMES SET SEQ = (SELECT COUNT(1) FROM NAMES WHERE TYPE = NEW.TYPE) WHERE TYPE = NEW.TYPE; END; ~~~ When I insert the first entry, it seems to work, but when I try to add a second one, it fails with > Runtime error: UNIQUE constraint failed: NAMES.TYPE, NAMES.SEQ (19) like this: ~~~lang-sqlite3 sqlite> select * from NAMES; sqlite> insert into NAMES (type, seq, name) values (0, -1, "test"); sqlite> select * from NAMES; 0|1|test sqlite> insert into NAMES (type, seq, name) values (0, -1, "test2"); Runtime error: UNIQUE constraint failed: NAMES.TYPE, NAMES.SEQ (19) sqlite> insert into NAMES (type, seq, name) values (1, -1, "test2"); sqlite> select * from NAMES; 0|1|test 1|1|test2 ~~~ So the trigger seems to work for each TYPE exactly once, but maybe it's not working, because the first SEQ should have been 0 instead of 1. I had also tried a BEFORE rather than AFTER trigger, but that would not change SEQ t all (using -1). What's wrong with the trigger, or is it the table definition?

I have the following query, which works fine: ~~~sql select Produkt.Name, max(case when Substanz.ID == 1 then Inhalt.Menge end) as "Histidin", max(case when Substanz.ID == 2 then Inhalt.Menge end) as "Isoleucin", max(case when Substanz.ID == 3 then Inhalt.Menge end) as "Leucin", max(case when Substanz.ID == 4 then Inhalt.Menge end) as "Lysin", max(case when Substanz.ID == 5 then Inhalt.Menge end) as "Methionin", max(case when Substanz.ID == 6 then Inhalt.Menge end) as "Phenylalanin", max(case when Substanz.ID == 7 then Inhalt.Menge end) as "Threonin", max(case when Substanz.ID == 8 then Inhalt.Menge end) as "Tryptophan", max(case when Substanz.ID == 9 then Inhalt.Menge end) as "Tyrosin" from Produkt left join Inhalt on Inhalt.Produkt = Produkt.ID left join Substanz on Inhalt.Substanz = Substanz.ID where Substanz.essentiell = 'ja' group by Produkt.Name; ~~~ I have read, that this is the idiomatic way to transpose a table. I am a bit annoyed that I have to specify the column names, although I have them as strings in a the table "Substanz". Is it possible to give a column a name based on a value in a table?