I have two tables that looks like this in their schema : create table PRODUCT_NOMENCLATURE(product_id integer, product_type varchar(100), product_name varchar(100)); insert into PRODUCT_NOMENCLATURE(…
Tag: sql
How does page overflow with columns (InnoDB) work in MySQL?
The documentation says: “Whether columns are stored off-page depends on the page size and the total size of the row.” 1.- This means that if I have a page size of 16KB, the maximum size of the row would be 8KB, therefore, if I have 4 columns, will the maximum size of each column be 2KB (approximat…
SQL: how do you calculate occupancy/ number of in-use for a facility?
I am trying to calculate the number of occupied lockers in the changing room. The dataset goes like this: My ideal output will be the number of occupied lockers per hour: I am able to manually calculate the number in excel (‘no. of check-in lockers during the hour’ + ‘no. of lockers in-use d…
addition within sum case when
How to sum all the customers in DS01,02,03 into DS? select sum(case when Ticket=’DS01′ OR ‘DSO2’ OR ‘DS03′ THEN customer ELSE 0 END) AS DS sum(case when Ticket=’MSO2’ OR ‘MS03’ THEN customer ELSE 0 END) AS MS from table group by ticket unsure how to …
Update script for a column with (max+1 for the column group value) on basis of another id column
As shown in the image. I have a table with Id, OrderId, SubId. For which the some row values in SubId column is zero as shown in the left table in the image. I have to update the SubId rows which are …
MariaDB using ‘distinct’ keyword changes expected behavior of other column
Why does this work: SET @cnt = 0; SELECT (@cnt:=@cnt+1) AS ‘foo’, title FROM employee; But when I add ‘distinct’ keyword, it does not, while distinct works on its own (not shown). SET @cnt = 0; …
Delete rows from table using JOIN – SQL Server
I have a table_A – id | name | is_active | —-+———+————+ 1 | jon | 1 | 2 | ham | 0 | 3 | gary | null | I have a table_B – id | name | -…
Django QuerySet annotate with Subquery
given the following model I’m trying to get, my Pizza and the number of toppings, along with the top 3 other pizzas (top in terms of the number of toppings) The final result should be: a list containing the current pizza, and the top 3 pizzas, along with the count of toppings for all. So right now I hav…
PostgreSQL array overlap with the ALL construct
I want to achieve the same behavior as in next example: select array[1, 3] && array[1] and array[1, 3] && array[2] using the ALL construct like so: select array[1, 3] && all(…
How to check if all instances of a DB row are the same and if so count + 1 in SQL?
I have the following SQLite DB table: app | vendor | active ——————— 123 | AGX | 1 123 | OTMA | 0 123 | PEI | 0 255 | IYU | 0 255 | MAG | 0 255 | MEI | 0 675 | IUU | …