I have a calendar table (c) with a set of 3 month dates: I have a Stats table (s) with views of each product on the website. I need to count the views per product per month whether there are views or not. I have followed many SO answers (SQL – Group By with Left Join) and I cannot see
How to update each entry of a table for each entry of another table in MySQL
I try to update a table by replacing text in it by a mapping table. The solution I came up with works, but only for the one entry. How can I update all entries of item for each entry in the id_mapping table? Example tables: item id type text 1 1 <span><a href=”item_id=111″>Link</a&g…
Single Query To Select Based On Parameters If Or not supplied
I have used SqlDataSource and have a select query based on District & Zone as below It’s an inline query. I have tried above query but not able to figure out how to Select condition based. I want if district supplied then Select according to District, if both District & Zone supplied then Select…
R – get a vector that tells me if a value of another vector is the first appearence or not
I have a data frame of sales with three columns: the code of the customer, the month the customer bought that item, and the year. A customer can buy something in september and then in december make another purchase, so appear two times. But I’m interested in knowing the absolutely new customoers by mont…
How to Add missing Months and display TotalPayment = 0
How to Add missing Months and display TotalPayment as 0 I already try row_number but it seems not working Query: Results: brgy month_dt TotalPayment barangay1 January 5345.00 barangay1 February 34232.00 barangay1 March 53454.00 barangay1 April 25234.00 barangay1 May 43224.00 barangay1 June 14.00 barangay1 Jul…
What SQL query can be used to limit continious periods by parameter value, and then to calculate datediff inside them?
I have a table of phone calls consisting of user_id, call_date, city, where city can be either A or B. It looks like this: user_id call_date city 1 2021-01-01 A 1 2021-01-02 B 1 2021-01-03 B 1 2021-01-05 B 1 2021-01-10 A 1 2021-01-12 B 1 2021-01-16 A 2 2021-01-17 A 2 2021-01-20 B 2 2021-01-22 B 2 2021-01-23 A
Better way to handle adding new table in a query which contains union of tables which have same column names but table name and data are different
I want to rewrite the query to get the data from all the tables, currently I have 12 tables which contains exact same column names but have different table name and content. To get all the records I am currently union them like below. Here I have given example of tables how I am using them, as you can see
COUNT to return NULL as 0 in same table in SAS PROC SQL with GROUP BY
I am having a task where I have 1 table. There is a unique ID for transactions, transaction dates, and store names. My task would be to count the number of transactions every store made during March. This, in itself, is simple: This would lead to a table where a store is present only if it had a transaction i…
Remove characters after space in sql
I have two tables as shown below: I would like to match these two columns i.e FIRST_NAME from table1 to FIRSTNAME from table2. I tried with the below query but only could match Richard and not Kristin. Please suggest! Answer I would add trim to make sure there’s no whitespace preventing a match.
Find 5 greatest totals after summing (quantity x value) for each qualifying user
I currently have a table which displays the inventory values of users. It works, but it displays all users and their inventory values in a random order. I’m wanting to limit the table to the top 5 descending inventory values only. This data cuts across three tables: DB Fiddle Users Inventories (contains…