Comments (10)
Can you share your schema (not the data) of the two tables.
My initial thought was that the view doesn't quite make sense - the view contains more columns than the group by, but that may just be me using SQL server too much...
from sqlitebrowser.
COUNT(Purchases.Product)
Isn't that just a count of the number of your purchases? (sales). So if you had ten sales (regardless of the number of products sold in those purchases) it would just be ten? I don't think this is the cause of your problem, but either way, its not going to give you the figure you're after. I'm trying to recreate this issue, but can't fathom how you're getting your values.
Also, count just gives you the number of rows. Does the field 'removed' contain how many items were purchased? So if fred made a purchase of ten items, and bob made a purchase of ten items, that would be two rows of ten in the 'removed' field? If so, count would be two, so you'd need the 'sum' keyword to get the true value of twenty.
from sqlitebrowser.
Ignoring my comments above, the exact filter is working for me on a view.
Edit: OK, it works on normal columns, but not calculated columns.
Its treating it as text, and the where clause is failing as a value of 2 doesn't equal a textual where of ='2' (which surprises me...)
Looking at the schema, db4s isn't correctly reporting the field type of the calculated column. I don't know if this is an issue with db4s or not.
The easiest solution is to override the filter and specifically tell it to filter on a numeric value of 2 not string value of 2.
from sqlitebrowser.
CREATE TABLE "Products" (
"Key" TEXT UNIQUE,
"Manufacturer" TEXT,
"ProductId" TEXT,
"ProductName" TEXT,
"Category" TEXT,
"Description" TEXT,
"Color" TEXT,
"Width" REAL,
"Depth" REAL,
"Height" REAL,
"PriceCurrency" TEXT DEFAULT 'CHF',
"PriceValue" REAL,
"PriceDate" TEXT,
"Photo" BLOB,
"Remark" TEXT
)
CREATE TABLE "Purchases" (
"Added" TEXT,
"Removed" TEXT,
"Dealer" TEXT,
"DealerLocation" TEXT,
"Product" TEXT,
"Currency" TEXT,
"Price" REAL,
"Location" TEXT,
"Remarks" TEXT,
FOREIGN KEY("Product") REFERENCES "Products"("Key")
)
The Purchases table contains the Products I bought. Sometimes one of these items get destroyed of gifted. In this case there will be a date in the removed column.
You may simplify
SELECT (COUNT(Purchases.Product)-COUNT(Purchases.Removed)) AS Count,
to
SELECT COUNT(Purchases.Product) AS Count,
but the exact filter is the same.
from sqlitebrowser.
from sqlitebrowser.
I don't know the internal logic of DB4S, but I'm guessing its seeing the calculated column and not knowing what it is .. text, integer, etc, so assuming text.
Try creating a view, forcing the type.
create view vwProducts2
as
select *, cast(key + product as INT) as derived
from products
This may then work.
Edit:
So for your use case, this would be
SELECT cast(COUNT(Purchases.Product) - COUNT(Purchases.Removed) as int) AS Count, Products.Manufacturer
from products
LEFT JOIN Purchases ON Products.Key = Purchases.Product
GROUP BY Products.Key
from sqlitebrowser.
It works, thanks for the support. This is a little bit strange because COUNT() should be already of type int.
Do you think that this is an sqlite3 issue? Still wondering why the log shows
SELECT NULL,* FROM "main"."ViewProductsPurchased" WHERE "Manufacturer" LIKE '%Ugears%' ESCAPE '' AND "Count" = '2' ORDER BY "Count" DESC LIMIT 49999 OFFSET 0;
and not
SELECT NULL,* FROM "main"."ViewProductsPurchased" WHERE "Manufacturer" LIKE '%Ugears%' ESCAPE '' AND "Count" = 2 ORDER BY "Count" DESC LIMIT 49999 OFFSET 0;
as with the other compare operators like >=.
from sqlitebrowser.
Do you think that this is an sqlite3 issue?
More of a Db4S issue. The filter probably (this is an assumption) looks at the field type and says, 'is this an integer? If so, I don't need to add quotes, otherwise I do', doesn't see anything, so based on the 'otherwise', adds quotes.
There are a couple of open issues around the filters, eg setting explicit values, which are kind of related.
Just found another 'workaround', rather than setting the cast type. Change the filter to just 2, and not =2. This then uses a 'like' operator which finds the record. However, this fails if you also have a count value of 22. Hmm. So not much of a workaround. Not foolproof, anyway. :(
from sqlitebrowser.
Setting this as a bug as someone more intelligent than me might be able to spot an easy fix.
from sqlitebrowser.
The cast works fine. Typically I'm only filtering for "=0" or ">0". But because "=0" didn't work I just used "0" as workaround ;-)
Just thought that I should raise the issue/bug here. Maybe it's really just an issue that evaluating COUNT() or the arithmetic operators doesn't forward the correct type. So COUNT() is always int and int+int is also int.
from sqlitebrowser.
Related Issues (20)
- [Bug]: Some UI text don't scale according to font size settings. HOT 3
- [Bug]: Assertion [cpMax <= pdoc->Length()] failed at ../scintilla/src/Editor.cpp 6096 HOT 3
- [Feature]: Fix ordering of schema export HOT 3
- [Bug]: creation of new db file upon attach of non-existing file fails. HOT 2
- [Feature]: "Filter in any column" should only filter in '__rowid__' is visible HOT 3
- Add Debug Information to 'Continuous' Release HOT 2
- Check Update for Unstable Build HOT 1
- Port to Qt 6 HOT 1
- Rewrite Build Entries in the Wiki HOT 1
- Support for Branch-Specific Build Variants HOT 1
- [Bug]: Importing CSV with column headers doesn't work if columns are not in same order as table in DB HOT 1
- Quick Browse HOT 1
- [Feature]: Comment Line Shortcut key for "Execute SQL" tab and Shortcut Key selection screen HOT 3
- [Feature]: auto reopen last database HOT 7
- No data returned from SQL query HOT 2
- [Bug]: Can't run nightly build on macOS 12.7.4 HOT 7
- [Bug]: External drives cannot be opened on Ubuntu HOT 3
- [Bug]: Execute SQL Console overlaps text HOT 2
- [Feature]: Allow NOT operator '!' in front of filters
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sqlitebrowser.