I have an assignment where I need to use aggregate functions in the queries. I keep running into a problem where there are multiply entries for the same ID, and I would rather them be combined into one run (added together for the same ID). Output below (IDs to be combined in column 3, “intTeamandClubID&…
Tag: sql
Is the Function in the GROUP BY Clause Necessary?
I’m an Oracle newbie just trying to learn. Are the following two queries equivalent in terms of their results? Query 1 Query 2: No TO_CHAR() in the GROUP BY Clause I’m using Oracle version 12.1. Answer Both queries won’t return the same result… unless customers.date includes only dates…
How to use this laravel query builder for this SQL Query
I have this query: SELECT `topic_posts`.*, `users`.`id` AS `adminID`, `users`.`name` as `adminName` FROM `topic_posts` INNER JOIN `topics` ON `topics`.`id` = `topic_posts`.`topic` INNER JOIN `users` ON `users`.`id` = `topic_posts`.`player` WHERE `users`.`playerAdminLevel` > 0 AND `topics`.`type` = 0 GROUP …
How to replace exist in Hive with two correlated subqueries
I have a query that looks like this I researched and read that in Hive IN or EXIST are not supported statements. I read that a workaround for this would be to use a LEFT JOIN. I have tried this but I am having trouble with the GROUP BY u.id. I read that this needs to be paired always with
Truncate table Dataverse
I am trying to truncate a table in Dataverse, so I have installed SQL Server Management studio, selected my database, and ran the query TRUNCATE TABLE tablename However, I get the error: Is there something that I am missing? Answer The SQL Connection to Dataverse is read-only The SQL connection provides read-…
how do i save a new student that contains entities, but don’t create these entities because they already exist in the database?
I want to save a student and a student has a TargetAudience object as an attribute. These target audiences are allready hardcoded in my database. (targetaudience = campus + major). Now when i post like this: it doesnt work because everythime it creates a new object for the campus and because i use name as a p…
Get Current Week/Month Dates
I want to make function get dates of current week. I will pass week start date number dynamically.Week start date can change.It can be sunday or monday or any day.I shoud be able to pass week start date number dynamically. If i Call function GetCurrentWeekDates(weekStartDay NUMBER,callingday Date),i should ge…
I am getting “Function not found: day at [5:84]”
This query results in the following error message: “Function not found: day at [5:84]” The syntax error in my SQL is also underlined as can be seen in this screen shot: Answer As mentioned by @Thorsten and @Samuel, you are getting this error as there is no DAY function in BigQuery. If you want to …
Need to get the value from a column whose column name is based on a value in another table
Table A has columns ID, COL1, COL2, COL3. Table B has columns AID, ColumnName. I need to get the [ColumnName] value in Table A based on the value of [ColumnName] in Table B. In the example below: For ID 1, I need to get the value of column COL1 (This is the value of [ColumnName] for AID 1 in Table
find timestamp difference between 2 columns with sqldf
According to this answer: https://stackoverflow.com/a/25863597/12304000 We can use something like this in mysql to calculate the time diff between two cols: How can I achieve the same thing with pandasql? I tried these: but they throw an error that: Answer From the PandaSQL documentation: pandasql uses SQLite…