New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
[Bug]: Exact filter on COUNT() column doesn't work #3608
Comments
Can you share your schema (not the data) of the two tables. |
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. |
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. |
CREATE TABLE "Products" ( 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. |
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 This may then work. Edit: SELECT cast(COUNT(Purchases.Product) - COUNT(Purchases.Removed) as int) AS Count, Products.Manufacturer |
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 |
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. 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. :( |
Setting this as a bug as someone more intelligent than me might be able to spot an easy fix. |
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 ;-) |
What did you do?
I created a view
CREATE VIEW "ViewProductsPurchased" AS
SELECT (COUNT(Purchases.Product)-COUNT(Purchases.Removed)) AS Count, Products.Manufacturer
FROM Products LEFT JOIN Purchases ON Products.Key = Purchases.Product
GROUP BY Products.Key
to count how often a product has been purchased.
Selected 'Exact filter' on a value of the Count column
What did you expect to see?
I expected that the list shows all rows containing the exact filter value
What did you see instead?
The result list is empty
DB4S Version
3.13.0-rc1
What OS are you seeing the problem on?
Windows
OS version
Windows 10
Relevant log output
Prevention against duplicate issues
The text was updated successfully, but these errors were encountered: