I have model Car, Listing and Website. I want to filter all car objects that have listing object on the given website but the listing needs to be active. To get all car objects that have listing object on the given website: But how do I filter out inactive listings? I want something like: Answer You would do …
Tag: sql
About “group function is not allowed here”
I have a table with job, salary and date columns. I am writing the following query in PL/SQL, but I am getting an error group function is not allowed here How can I solve this problem? Answer Your query makes no sense (to me, at least). What does that average salary represent? Whose average salary? Here’…
presto sql filter part of the string java
I have a string in col1 select col1 from table “blah blah 123 blah iterate 866 loss blah blah” how to filter string between iterate and loss and just show sum of all such filtered numbers ? Answer You can use regexp_extract to extract the number within that string, convert the values to a numeric …
Categorize the total amounts
I have a query that gave me a list of store names and its respective amounts. However I would like to sum and categorize the amounts like this: Store Name | $0-$50 | $50 – $100 | $100 – $500 | $500+ Based on the following screenshot: Here is my condition: Here is my basic query for the above snaps…
Is there an elegant way to aggregate data by groups based on elements of an array in SQL?
I’m looking for a way to aggregate data by groups that are based on elements in an array. I’m working with product and sales data in a BigQuery warehouse, and want to measure the sales of collections of products, where each product may be in more than one collection. This is probably best explaine…
How can I extract all text between single full stops (i.e. ignore `…`) with a re2 regex?
How can I extract all text between single full stops (i.e. ignore …) with a re2 regex? I am using the REGEXP_EXTRACT_ALL function in BigQuery which uses https://github.com/google/re2/wiki/Syntax. From the following example: This is… a.. sentence. It is just an example. I would like the query to ex…
List count for last 12 months broken down by month
I have a query that can get a total active count of products until a specified date @POINT Output for @POINT “01/01/2021” is From a table like How would I remake this query so that I can have a list of points (last 12 months) like in one query? I don’t want to create a separate table of date…
How to create a SQL function to parse multiple xml formatted strings?
I am trying to parse a price from a price column in SQL. I have already create a function that will parse the price but it only works if “all” price strings are the same on all items. I realize now that I need a more robust function that can parse out multiple price configuration. Here is my simpl…
Select one existing row order by where clause
I have a table like that: ID | lang ——— 1 | ru 2 | en I want to select 1 top existing row from my where clause. Not ordering by id or by lang, but ordering by my where clause. where lang in (‘…
SQL data types for AnyLogic
I am saving the output of my AnyLogic model into an SQL server database. For non-AnyLogic aficionados, AnyLogic is based on Java. However, I am not sure what data types I need to specify for my columns in the database. So far I am using these: double in AnyLogic : float in SQL string in AnyLogic : varchar in …