Suppose I have a denormalized table that includes an ID and a value that I need to count. Something like this: In this case, select Tree_ID, count(Count_If_True) from Table group by Tree_ID would show: But If I denormalize my table further with a join from an Apples table (where every tree has multiple apples…
Tag: sql
How can I group data from multiple rows into the same row?
I have a query: That returns I want to combine the rows and take one value from each column (doesn’t matter which, first, last, can be any) So one elementary school, one high school, one university. It would look like: However, when I try to group by: I’m getting only a value for one of the column…
How to add fictional rows to an SQL results in Oracle without à grouping
here is my issue, I’m working on an existing big report and they want me to add fictional rows with specifics values each time row in database meet a condition (let’s say status = Canceld) I simplified the query (1000 lines of SQL Code) to this : if I have two table A and B : and the query is
query to update records count based on relation and constraints
Im trying to update a column (address_count) on my people table that must have the sum of records from another table (people_addresses) plus some constraints. The idea is that, the record on people must exist on people_addresses and must be present on at least one of the other tables (table a, table b) With t…
GROUP by data by time range in postgresql
I want to GROUP by data by time range. The example I have start_date and end_date, and I want the separate range between start_date and end_date on 25 range and get sum value from 1 to 25. Simple presentation of my table: table t1 have: generate_series function to separate on and sum by how this 25 for 2019-1…
Count of line item in MySQL
I have a complex query joined to multiple tables to show a “transaction history”, I want a quick and easy way to count the occurrences of order_id to make it as the line_number. I had it done with code but for performance reason I want to have it done with MySQL. Here is an example of what I have:…
Get all days in a month excluding weekends postgresql
I want to write a query sql for postgresql that can basically return me all days of a month excluding weekends. For example (For 11/2019) : First Week: 11/1 Second Week : 11/4 -> 11/8 Third Week : 11/11 -> 11/15 Fouth Week : 11/18 -> 11/22 Fifth Week : 11/25 -> 11/29 I can’t find any postgre…
Get the dates of two weeks from today from database
I have some dates in postgresql database. I want to find dates from today to next two weeks or 14 days. How i can find the dates between current date and next 14 days? This query is not working. I …
LEFT JOIN returning no results if a WHERE clause is added?
I’m trying to add the dbo.EmpHRSchedule.Type = ‘HOLIDAY’ entries to the Hours column only if they exist. To my understanding, a LEFT JOIN would only return entries if they match, and return NULL for non-matches. When I uncomment the AND ehrs.Type = ‘HOLIDAY’ line, no results are …
Ranking subcategories while keeping order of ID
I have the following table: I would like to rank the subcategory (cat2). Desired outcome: I use DENSE_RANK with PARTITION BY to get the following result: Statement: As you can see, the only thing I’m missing is the order of the result. Currently, the rank is based on the alphabetic order of cat2. Howeve…