Hi everyone, I have two tables as follows PRODUCT table Id | Name | Price And an ORDERITEM table Id | OrderId | ProductId | Quantity What I'm trying to do is, calculate the subtotal price for each product (Quantity*Price) then SUM the TOTAL value for the entire order.. I'm trying something like this SELECT Id, SUM(Quantity * (s...
I have the following SQL that returns the max BILL_DATE based on some criteria. BILL_DATE is defined in the database as a DATE. SELECT MAX(BILL_DATE) FROM BILLTABLE WHERE col1 = ? and col2 = ? But when I read the value from the resultSet. bill.setBillDate(resultSet.getDate(1)); An exception is thrown: Invalid data conversion...
The following SQL statement works in MySQL but not with Oracle: SELECT *, MAX(COLUMN_A) FROM table_xyz WHERE COLUMN_A <= 100 GROUP BY COLUMN_A Oracle complaint: "FROM keyword not found where expected" ...
What is the best database model to store user visits and count unique users using the IP in a big database with 1.000.000 rows for example? SELECT COUNT(DISTINCT ip) FROM visits But with 1.000.000 diferent ip's it can be a slow query. Caching will not return the real number. How big stats systems counts uniques visits? ...
The setup is like this: Col1 Col2 12345 12 12348 14 20145 16 00541 Null 51234 22 Simplified, obviously. What I want to do is update Col2 wherever it's Null by setting it to the Col2 value for whatever has the closest value in Col1 (so in this example, row four should have Col2 set to 12). This is...
How can we find percentile, skewness and kurtosis in MySQL? Is there any formula? I want to find the above three for a large number of data records. Thank you, J ...
I'm writing stored procedures for the first time, and it's not working. The trouble is I can't see a reason for it not to work. I'm running it through phpmyadmin 2.8.2.4. My MySQL version is 5.1. Here is the first part of the query: create procedure under_user_type (in original_post int, out user_type int, out user_id longtext) begin ...
Can anyone recommend a good tutorial (or book perhaps) that covers advanced topics in the use of Analytic Functions? I'm looking for something that covers both Oracle and SQLServer - or two separate guides if a single one doesn't exist. Something that offers good examples of using nontrivial functions (LAG, LEAD, GROUPING, PERCENTILE) ...
Given a table like so: id | value ------------------- 1 | food 2 | foot 3 | barfoo 4 | bar 5 | baz Using postgres I want to find all of the rows where the value field matches from the start of the search field. Sort of like SELECT * FROM table where 'foo' ilike value% Searchi...
This query returns the sum of "closed" daily sales for a particular salesperson within a particular date range: SELECT SUM(price) as closed_total FROM dbo.Sales WHERE salesperson_ID = @salesperson_ID AND date_ordered BETWEEN @start_date AND @end_date AND closed = 1 GROUP BY date_ordered The output looks like:...
I'm trying to alter the results of a Transact-SQL (MS SQL Server) report that is currently showing: CompanyA DVCE3 CompanyB DVCE2 CompanyC DVCE3 CompanyD NULL CompanyE DVCE3 and I would like it instead to show: CompanyA 36 CompanyB 24 CompanyC 36 CompanyD CompanyE 36 I'm sure the answer to...
After searching stackoverflow.com I found several questions asking how to remove duplicates, but none of them addressed speed. In my case I have a table with 10 columns that contains 5 million exact row duplicates. In addition, I have at least a million other rows with duplicates in 9 of the 10 columns. My current technique is taking ...
I'm creating a CLR user defined function in Sql Server 2005 to do some cleaning in a lot of database tables. The task is to remove almost all tags except links ('a' tags and their 'href' attributes). So I divided the problem in two stages. 1. creating a user defined sql server function, and 2. creating a sql server script to do the upda...
I'm thinking of the best way to design a database given the following situation. There are over 1000 hotel room units. Each hotel room unit has a list of about 100 individual types of items with a qty attached to them. The inventory is standard for each hotel room. But the qty of each item can be changed. For example 3 coffee mug...
I have the following error: no such column: company_name: SELECT count("contact_emails".id) AS count_id FROM "contact_emails" The model ContactEmail does NOT have a column company_name. I created it as a virtual attribute. It's not possible to do a select based on that information? How would I do it, then? ContactEmail belongs_to...
what is the wrong in this Query ? string command_get_pay = "select Credit from Update_Company_Credit where (Update_Date LIKE '%" + System.DateTime.Today.ToShortDateString() + "%')"; ...
According to http://www.storytotell.org/blog/2008/11/14/literal-tables-and-updates-with-joins-in-sql.html the following is valid: SELECT * FROM VALUES ('Lisp', 50, true), ('Scheme', 30, true), ('Clojure', 1, true) AS languages (name, age, lispy) But it doesn't appear to work. The best i c...
SELECT patron_name, producer.fed_number, tax_number, average_tb_test FROM producer INNER JOIN producer_details ON producer.federal_number = producer_details.federal_number INNER JOIN statement ON producer.patron_number = statement.patron_number WHERE producer.patron_number = @PatronNo (SELECT MAX(statement.statement_number), MAX(p...
Hello, I have a column named Sysdate in several tables, which is causing SQL errors when I try to Select it. It works if "Sysdate" is enclosed in double-quotes, e.g. SELECT "Sysdate" FROM table1 When I try to use aliases it causes syntax errors: SELECT t1."Sysdate" FROM table1 AS t1 Please advise. Thank you, Max. ...
what is the syntax of sql query has intersection between 2 "where Like" condition string command_get_pay = "select Credit from Update_Company_Credit where ( Update_Date LIKE '%" + 8 + "%'& Update_Date LIKE 18'%" + 8 + "%')";` is this true I try it but I got exception in syntax ...