0
votes
1answer
17 views

PL/SQL group orders by date and sum the total

I need to group orders by date and sum the total, My table looks like this : TEST_DATE TEST_ID - NUMBER SUCC_ID - NUMBER I need to group by date and sum the total, this is my query: SELECT ...
0
votes
1answer
13 views

Count(*) as rolecount, filter by rolecount = 1

How can i filter out the rows who only got rolecount = 1? WHERE rolecount = 1 gives an error. Can't really figure out how to do it. SELECT u.uid AS uid, u.name AS name, u.picture AS picture, ...
0
votes
1answer
20 views

Is there any performance difference between a group by statement and nested select statements in sql?

I am curious to know about the differences between using group by statement and nested selects to achieve the same result. for example consider I have two tables such as the following: tblPurchase ...
0
votes
1answer
23 views

Bad Design or Complicated Query?

Problem: For fun I am building a Database for lottery results to get some statistics off of. As of now I am only interested in a Pick 3 style lotto and might introduce more later. How would you ...
-2
votes
1answer
18 views

Error arithmetic overflow error converting expression to data type int

when i check report of sql server it show, Error, arithmetic overflow error converting expression to data type int. How to solve this problem.?
1
vote
0answers
8 views

Joining timestamped records with the most-recent-prior records from another timestamped table

Say I have 2 tables: Table: sales timestamp timestamp without time zone amount numeric Table: prices timestamp timestamp without time zone price numeric How can I get a ...
2
votes
1answer
506 views

Simple SQL Query

I have a db that in one column have 5 possible values: "Fechado", "Aberto", "Em Despiste", "Cancelado", "Resolvido". And im trying to do a query that counts the values to a json file var data = ...
3
votes
1answer
31 views

Copy database and clear down data (including linked tables) older than N days

I have a sizable production database (140GB) on MS SQL Server 2012, which I'm trying to automatically download to our development environments on a periodical basis (every week or on demand). I'm not ...
0
votes
1answer
23 views

how to optimize grouped select from an already grouped select query

I already had a specific query to get a number of shops, but I couldn't sum the cost field, I kept on getting an error on the sum function, but it worked when I did this; select tab.shop_id, ...
0
votes
0answers
34 views

Nesting multiple INNER JOIN within LEFT OUTER JOIN in sql on deifferent side of keyword

I am having some problems with one of the question's answered in the book "SQL FOR MERE MORTALS". Here is the problem statement Problem Statement Here is the Database Structure The Database ...
0
votes
0answers
20 views

Multiple Queries with overlapping results and column level permissions

Sorry for the obtuse Title, I'm having a hard time knowing how to summarize. I'm trying to do this with pure SQL(mySQL) and server-side(Perl) data manipulation. A user table is defined: id, uid, ...
2
votes
3answers
437 views

How do I ORDER BY typical software release versions like X.Y.Z?

Given a "SoftwareReleases" table: | id | version | | 1 | 0.9 | | 2 | 1.0 | | 3 | 0.9.1 | | 4 | 1.1 | | 5 | 0.9.9 | | 6 | 0.9.10 | How do I produce this output? | id | ...
1
vote
2answers
42 views

Determining what query was called

I want to reverse engineer what exactly happens to the MySQL database when a product is added in Magento. I will have to reproduce the query, because I would like to manually enter new records using a ...
0
votes
1answer
36 views

procedure to select the first name of the passed id

I have a table person_name DESC PERSON_NAME Name Null Type ------------- -------- ------------ ID NOT NULL NUMBER(9) FIRSTNAME VARCHAR2(20) ...
1
vote
1answer
23 views

optimize the query with big and low cardinality

I have big enough dataset and few joins to get them all with help next query: SELECT DISTINCT c.c_id FROM c_active z1 INNER JOIN cs c ON (z1.cv_id=c.cv_id) INNER JOIN indi i ON (c.m_id=i.m_id) INNER ...
-2
votes
0answers
23 views

How to create an attendance system and split the time for every employee per day [duplicate]

I have this table in my database and I want to split the table. ID PID date time 1 12 10/04/2014 7:00 2 12 10/04/2014 10:00 3 12 ...
0
votes
0answers
43 views

Combine two mySQL sum queries into one which use different where clauses

I have two sum quires referencing different tables in mySQL. How can I combine the two in order to calculate the difference between the two values for each user in the database? Query 1: SELECT ...
1
vote
1answer
38 views

Restoring a database, changing recovery model

I regularly make a backup of my production database, then restore the same onto a test database, so developers can always have recent data for testing. The production database uses full recovery ...
0
votes
0answers
34 views

Update table from different table

UPDATE RBI.RBI_OV_F_DETAIL A SET (BRANCH_ID, BRANCH_NUMBER) = ( SELECT PRICE_BRANCH_ID, PRICE_BRANCH FROM RSE.RSE_SO_F_ORDERS B WHERE A.SOURCE_ID = ...
0
votes
1answer
86 views

Why straight SQL query run faster than stored procedure?

I am facing-out a very surprising problem in SQL Server. The problem is when the query run in SSMS window it will return result in ~1 sec and when the query run through a stored procedure it will ...
0
votes
1answer
48 views

Is this statement syntactically correct?

$q = "SELECT var1, var2, var3 FROM tablename WHERE var_a = '$var_a' AND var_b = '$var_b' AND ( ( var1 = '$var1' AND var2 = '$var2' ) OR ( var1 = '$var2' AND var2 = '$var1' ) )"; This ...
0
votes
1answer
10 views

Update query for Minimum absolute difference in Access [SQL]

I have 3 tables in an Access db. Table_1 and Table_2 are where the source data comes from and I am filling Table_3. Table_1 has the following fields: Acct_Name Amt Table_2 has the following ...
0
votes
1answer
37 views

Logical problem with a database model

Beforehand the table names and columns are in spanish, my native language is spanish. I've got a database to manage the tickets sales of a trip that is made by a bus, the problem is that when i relate ...
0
votes
1answer
29 views

MySQL add few column exception for TRIGGER UPDATE FOR EACH ROW

How to effectively add column id, name exception for trigger: CREATE TRIGGER updated BEFORE UPDATE ON table1 FOR EACH ROW SET NEW.date=NOW() so that date=NOW() will be affected on updating any ...
2
votes
1answer
177 views

SQL Parameter Sniffing Possible Workaround

Our system has roughly 500 "Customers" that have extreme differences in record counts. Here is a (greatly simplfied) sample query that could return 0 - 100000 rows depending on the parameter passed. ...
0
votes
0answers
14 views

Pasting into PHPMyAdmin

This is probably really simple but I have had a look on the web but can't seem to find an answer. I am experimenting with MySQL + PHPMyAdmin on a WAMPserver stack and was saving queries in Excel When ...
1
vote
1answer
68 views

Reducing execution time for this gnarly SQL

I'm creating an order system of some kind. There is a function that I use to get orders (in PHP) and retrieve the required information about the order. However, it seems that I am pulling a lot of ...
1
vote
1answer
40 views

Update different columns on different clauses

I have a database table in Postgres 9.3 with the following layout: id SERIAL, col1 INT, col2 INT Whenever a new row is inserted, I will have to update ALL rows col1 and/or col2 in different cases ...
0
votes
0answers
47 views

Showing employee on leave [on hold]

How can I show an employee which is absent and leave and absent but not in leave here I can show which employee is on leave when there ApproveStatus is true and I can give date manually from which ...
0
votes
0answers
15 views

How does database connection actually works? [duplicate]

I am wondering, on a practical level : what is a database connection? What does it represent for PHP/Java/Ruby/etc to have an opened connection for instance? What about the MySQL deamon? Does it ...
-2
votes
0answers
31 views

Is it possible to do simple division or multiplication in Select Statement? and then insert into new table or create permanent view? [migrated]

I want to calculate the buy/sell ratio based on the two items/values obtained in the select statement, is it possible? Thanks. SELECT Ticker, sum(dBuyAmount) as Buy, sum(dSellAmount) as Sell, ...
0
votes
1answer
29 views

How to sum the buy order and sell order in one row per each ticker

I have a table containing some trading information of several tickers and I would like to divide the data into sum(Amount) for buy and sell separately, grouping by each ticker. I have one table ...
-1
votes
0answers
45 views

How to select record with biggest value of column [migrated]

Columns: numberIWant, value I would like to get numberIWant number of records, which have biggest value. select max(value) from myTable only returns the max value. I want to find numberIWant that ...
1
vote
1answer
17 views

Access Value between ranges

I'd like the following population logic built in an Access database: I have two tables. The first, Table1 contains a set of buckets, with a starting and ending value: Start End Bucket ...
0
votes
3answers
39 views

Select rows where 2 cells not the same

I have the following table: +----+-------+---------+---------------+ | id | owner | variant | EAN | +----+-------+---------+---------------+ | 1 | 101 | black | 1111111111111 | ...
0
votes
2answers
30 views

SQL Server 2012 - Insufficient memory to continue the execution of the program. (mscorlib)

I have a sql file, which is up to 180 Mb. When I try to open and execute it on Microsoft SQL Server Managment Studio 2012, I am getting this error. . What do do ? I researched on Google. And there ...
1
vote
1answer
40 views

Maintenance plan backup and reload the same database

I want in my maintenance plan every night to copy the production database to a backup, and reload the newly-created backup into a test database. By reading here: Restore database - maintenance plan ...
-1
votes
2answers
41 views

How to merge a column in single row in sql server

I have a following data: Comp_id | Cntm_nb | Seq_nb | Amount | Type 1 | 1 | 1 | 2000 | rent 1 | 1 | 1 | 200 | charge 1 | ...
0
votes
1answer
32 views

How to check if current SQL session has uncommited changes?

Say you are in SQL Developer and want to check if the current user session (i.e. your connection) has uncommitted changes. If you quit SQL Developer, it does such a check and displays a dialog box ...
1
vote
1answer
38 views

Group by alternative

I have a table , that has a generic set of actions for EVERY user. However if there is a user that needs to have a unique action, that replaces an existing action, then in my query, I want to ...
0
votes
1answer
34 views

Query Rows That Have a Following Row That Meets Criteria

Will try to do my best to explain this, as I don't really know how to word this to search for an answer. I'm a constantly learning but still newish SQL user who thus far has been able to solve most ...
0
votes
1answer
40 views

Delete from table where not in another table using two columns

I am trying to get my head around this delete statement I am trying to accomplish. I understand how to delete from table where column not in ( subquery to table2) I have two tables with multiple ...
1
vote
1answer
62 views

SQL Server Multiple JOINS with Table Value Function - query never ends

I have a query with 4 joins using a table value function to get the data and when I execute it the query never ends. Issue Details Table valued function: CREATE FUNCTION [dbo].[GetIndicator] ( ...
0
votes
1answer
79 views

Splitting Columns

I want to ask a complicated question and I think its advanced a little. I have a table has this design, its an attendance system. I need to split the time if the attendance count per day was not 2: ...
-6
votes
0answers
54 views

Query For Leave and Absent [closed]

SELECT DISTINCT subDate.absent_date, SMEmployee.EmployeeID, SMEmployee.BadgeNumber, SMEmployee.EmployeeFullName, SMDepartment.DepartmentName, MIN(CONVERT(time(0),CHECKTIME)) as Intime, ...
1
vote
0answers
20 views

Can I include extra columns in a m->m mapping table? [duplicate]

I guess this is sort of an odd question, but consider this scenario. table classes (id_pk) table students (id_pk) table students_classes_map (students_id_fk_pk, classes_id_fk_pk, finalExamScore ...
0
votes
2answers
69 views

How can I make this query more efficient?

This is the query: SELECT races.*, tmptimers.last_start_time, tmplaps.updated_at AS last_updated_at FROM races LEFT JOIN (SELECT * FROM timers WHERE timers.user_id = 1) AS tmptimers ON ...
0
votes
1answer
17 views

How to find the last error codes and messages of failed statements executed by a user?

Hopefully, a program that connects to a Oracle DB does sufficient logging of errors on its own, e.g. when doing a bulk insert the error reporting of a program could produce something like this: ...
0
votes
1answer
28 views

Missing Database Diagrams node in Management Studio [on hold]

It seems that I dont see this diagrams node when I log into my database in the testing SQL Server 2008R2 (on a 32 bit server) but when I connect to the same instance from the production SQL Server (64 ...
2
votes
2answers
65 views

Database events that will cause deletion of index

In SQL server if we try to change a data type of a column having index ,then it deletes the indexes associated with that column and changes the data type . I was not aware of it and was faced problem ...