I have a table like the following: I want to convert the data to wide format like the following: the SQL I used does not have a pivot choice so I am wondering how to convert the long format to wide without using PIVOT. Answer If I understand your question correctly, you can do conditional aggregation: Maybe y…
Tag: sql
Variables in SQLITE Python
I am working in Jupyter Notebooks using pandasql, which uses SQLight syntax. I am trying to select entries from a certain month, depending on a variable. I am planning to create a Python function that will change the value of the variable being used, but right now I am trying to get “Parameterized Queri…
SQL find max date based on a non null other column
I have a table like so: And I would like to return an entire row containing the uniqueID, scandatetime, and latest scanfacilityname (i.e., max scandatetime where scanfacilityname is not null). I have tried the following query: But im not sure if this is correct/if I need the coalesce. Answer You can use the m…
SQL Server – Calculating target vs actual values per week for sales overview
I am new to SQL. I am trying to create an over view using these 2 tables Actual sale Week_Year Target sale To combine into this resulting view with Targets and Actuals: where column Target is ‘Total to be sold’ spread linearly between the available weeks. How can I achieve this using SQL Server? A…
UPPER and LOWER functions in Informix DB LIKE queries do not work with all entries in the table
Background information: IBM Informix Dynamic Server Version 12.10.FC14WE DB_LOCALE=et_EE.utf8 Table has an ID primary key, no indexes Column datatype lvarchar (-1) The table column has names like “FIRST COMPANY”, “SECOND COMPANY”, “Third”. To find name matches the UPPER fun…
How to write this kind of query in Oracle
I have a MySQL database and I want to migrate all query to Oracle and since I am not an expert in Oracle SQL I am stuck here. I have query like this My question is: How to write this ORDER BY expression in Oracle SQL? Answer You can use There is no column naming format containing back ticks in
Invalid Number Exception in SQL when trying to call select statement from Java
I am trying to call a select query from my Java Program but i am getting Invalid Numbererror. I am trying to call the query something like this code I am getting query something like this: Result I am getting the error Invalid number in the line P.PACKAGE_ID = {0}. I gone through and come to know that if i
Can’t return column table when the table have just FK?
When I choose a table when it doesn’t have any Primary Key, my query returns nothing. So what’s the problem in this query? This is result when I have pkin my table: And this is result when I haven’t pkin my table: Here is the query: Answer As you are putting INNER JOIN with PRIMARY KEY const…
What are difference between KEEP and OVER in Oracle analytic SQL [closed]
Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers. We don’t allow questions seeking recommendations for books, tools, software libraries, and more. You can edit the question so it can be answered with facts and citations. Closed 2 years ago. Improve this ques…
Rails ActiveRecord – Order query by matching number
I have a model called Location. This table has 2 fields I want to order by: rating (integer) and name (string). I want to order alphabetically by name, but if the rating is 5, I want that rating to come first. If the rating is anything else except for 5, I want them to unbiasedly show up in alphabetical order