Table: user id compId 1 comp1 2 comp1 Table: Company id name comp1 coke comp2 pepsi need a MYSQL query which should fetch company record only if it has one or more users, when passed a company id. I will have other where conditions on company table. Can this be achieved by joins? example 1: query(comp1) resul…
Tag: sql
Replace “OR” on 2 indexes with a faster solution (UNION?)
I’m querying shopping-carts in a shop-system, like: I need to query records of c with a column which shows the number of carts which have the same user OR the same email. So I do: This works, but the problem is that the OR is very slow, because MySQL/MariaDB doesn’t use any key in the subquery: Ev…
Average per hour adding an empty record if data is missing
I have this data: I need to store the average of each hour in a second table. I don’t know if I can also insert a NULL record if there is no data for an hour, maybe in a second treatment. I have tried with this first treatment but can’t do it. I get an error message and don’t know
SQL group and condition sum in the last row
I have several items which belongs to different group, some are unknown group. I need the sum of each group by date. I just try below SQL but still not exactly what I want: DB: SAP HANA Data: Item PG NET_VALUE_USD EMANAGER_DATE 1 1 100 2021-09 2 1 200 2021-10 3 2 300 2021-09 4 2 400 2021-09 5 3
How to debug the issue of index monitoring wherein index entry is not found in table v$object_usage?
I am facing an issue while enabling monitoring on indexes , I executed following command to enable index monitoring and then checked the entry in v$object_usage view but couldn’t find any record in it : O/p is Index REPORT.DY_SUM_DLY_SCH_TRN_DIV altered. Checking the entry in v$object_usage : In output …
SQL query to find the second occurrence of an element in a database
For example I have this table: I want to get the row where the PersonID occurs for the second time. So the desired output needs to be (for one of the person ID) : What would be the query for this scenario? Answer Use ROW_NUMBER() Function For this scenario Please Refer this link for how to use this function d…
Why my after update trigger is executing on insert although I have separate trigger for insert as well
Answer First of all – for the INSERT – I would use a DATETIME2(3) column with a default constraint – then you do not need a trigger: Now, each time you insert a row and you do not specify the dtEnter column in your list of columns to insert values into – it will automatically be set by…
Get max date based on another column in SQL
I have a table variable which consists of columns id, date and status as shown below And follows is the sample data Also declared a variable as shown below. From this table I need to get the row which containing the maximum value of date( MAX(dates) ) where status is ‘ABC’, from the above sample v…
counting consecutive months in sas
From the sample data below, I’m trying to count how many consecutive months a member has. In the event if an ID has a gap month in the middle, the output should show the most recent count of consecutive months this member had, see example below. dataset=one ID Month 72 01SEP2020 72 01OCT2020 72 01NOV202…
Zero lead digit for a number removed when concatenating in SQL
I have written a couple of PL SQL Functions to return a currency and amount from a compressed field. These work perfectly individually. e.g. returns GBP returns 0.1 Concatenating the 2… returns GBP.1 I want it to be GBP0.1, any idea what is going wrong? The amount function returns a NUMBER the currency …