Tag: sql

MYSQL inner join counting only once depending on two conditions

I have a SQL query where I’m joining three tables that have information about employees, their departments, their schedules, if they are in vacations or not and reasons why they skipped work on an specific date. In one of the tables I have as foreign keys the employeeId, the scheduleId and the date in w…

How to add a row to result if a condition based on two adjacent rows is true

postgresql sql

Given a table like this Is it possible to get this? Right now, I’m post processing the table with a for loop checking if system_time_start is not equal to the previous row’s system_time_end, then insert another row to the result with a null price, but I’m wondering if there’s a way to …

1064 You have an error in your SQL syntax; check the manual … for the right syntax to use near ‘WHERE reviews.yes_no = yes

database laravel sql

I want to optimise the queries on my project and after many tentatives of using eager loading with datatables i just give up because there was no query reducing at all. Anyway now Im trying to achieve that using join, but the problem is that using WHERE clause in DB::RAW it gives me the following error: &#822…

SQL Grand total without subtotals

rollup sql sql-server

I’m making a large SQL report in Orderwise, very roughly simplified as follows; I want a grand total at the bottom, without a bunch of subtotals dotted in throughout the report – therefore I don’t think I can use ROLLUP. The Subquery in there is of course a sub query and in the real thing th…

Error Code: 1826. Duplicate foreign key constraint name ‘menu_ibfk_1’

compiler-errors error-code mysql runtime-error sql

I am writing a code on MySQL and it’s giving me errors. This is my code and it’s giving me the error – “Error Code: 1826. Duplicate foreign key constraint name ‘menu_ibfk_1’ ” When I give a different constraint name, it give me the error – “Error Code: 182…

Select Top X records starting at record Y

ms-access sql vba

I am using MS Access VBA to make a few queries. I have a list of about 4000 items and I need query 1 to be the top 739 items and query 2 to be the top 428 items starting at record 740 and I need query …

Percentage distribution of column occurence?

postgresql sql

I am looking for a way to calculate the distribution of column values in my table. e.g. if I have two rows containing “red” and “blue”, each should have 50%. I want to count the occurence of col and compare that to the overall amount of rows. My attempt: Any help? Thank you! Answer Jus…

Find the first AND last date that an ID occured in dataset SQL

mysql sql sql-view

I have a very large data set with over 1 million rows and over 70 columns. The dataset contains data about back orders of an organization. Every day a snapshot is being made and all orders that are backorders are added to the dataset. I want to know what the first date is when an OrderID occurs in the dataset…

How to use IF statement in a calculation in a SQL query

mysql sql

I use following query to give me the earned commission of our sales agents. Now I want the output to be sorted by (sales * sph * factor). Where factor is 10 if sph > 1.5 and else 7.5. Is it possible to implement this in the query. If yes how do I achieve this? It would also be good