SQL: how to get a weighted count using GROUP BY?
How can I make a query like: select myvalue, count() as freq from mytable group by myvalue; But where freq sums values from a weight column instead of counting each row as 1? I'm using MySQL 5.1. ...
sql
SQL: Get list of numbers not in use by other rows.
I'm using PostgreSQL 8.1.17, and I have a table with account numbers. The acceptable range for an account number is a number between 1 and 1,000,000 (a six digit number). The column "acctnum" contains the account number. Selecting all the numbers in use is easy (SELECT acctnum FROM tbl_acct_numbers ORDER BY acctnum). What I would like to...
Selecting SUM of TOP 2 values within a table with multiple GROUP in SQL
I've been playing with sets in SQL Server 2000 and have the following table structure for one of my temp tables (#Periods): RestCTR HoursCTR Duration Rest ---------------------------------------- 1 337 2 0 2 337 46 1 3 337 2 ...
Grouping data in the select statement
I have huge data which needs to be classifed in to different groups while retrieving. Each group has a different condition. I don't want to retrieve them separately. I want to know the number of items in each group using a single sql statement. For example, the pseudo code will be like this: Select count(IssueID) as Issue1_Count if(...
More efficient double coalesce join alternative
I have a procedure with a (slightly more complex) version of the below: CREATE PROC sp_Find_ID ( @Match1 varchar(10), @Match2 varchar(10) ) AS DECLARE @ID int SELECT @ID = ID FROM Table1 WHERE Match1 = @Match1 AND Coalesce(Match2,@Match2,'') = Coalesce(@Match2,Match2,'') SELECT @ID ID Essentially Match1 is a mandatory m...
Why am I getting "Unknown Column" error?
The exact error I am getting is: "Unknown column 'trans_paid' in 'where clause'" My query ($from_date and $to_date are correctly formatted): SELECT o.order_id, o.order_po_no, o.order_ship_date, acct.acct_company, SUM( ROUND( i.item_qty * i.item_price, 2 ) ) AS item_amount, ( SELECT SUM( trans_amount ) FR...
How should i modify this SQL statement?
My SQL Server view SELECT geo.HyperLinks.CatID, geo.Tags.Tag, geo.HyperLinks.HyperLinksID FROM geo.HyperLinks LEFT OUTER JOIN geo.Tags INNER JOIN geo.TagsList ON geo.Tags.TagID = geo.TagsList.TagID ON geo.HyperLinks.HyperLinksID = geo.TagsList.HyperLinksID WHERE HyperLinksID = 1 returns these... HyperLinksID...
SybaseDB , change the default value of an existing column in a table.
I have a table called "downloads" with a few thousand rows. I just added a column called is_completed using the following command: ALTER TABLE downloads ADD is_completed BIT default 1 NOT NULL Now I would like to change the default value for is_completed to 0 - I tried this command to no avail: ALTER TABLE downloads MODIFY is_complet...
Getting Parent of Parent in Self Join Table
I have self join table. This table is being used to join up to 4 level, i.e.; Region -> Country -> County -> Town How can I get Parent of Parent of Town. To do this up to two level this is the query SELECT t.ShortName AS Town, (SELECT c.ShortName FROM Locations c WHERE c.LocationId = t.ParentId) AS County FROM ...
SQL Server 2005 SUM
Hey all, this is my query string here: SELECT SUM(Total) as Total, AdministratorCode, SUM(WOD.Quantity) as thePass FROM tblWO as WO, tblWOD as WOD WHERE WOD.OrderID = WO.ID AND WO.OrderDate BETWEEN '2010-01-01' AND '2010-08-31' AND Approved = '1' ORDER BY WO.AdministratorCode But i keep getting the error: The ...
NHibernate Collection Left Outer Join Where Clause Issue
It seems that when using the following NHibernate query, I do not get a root entity when the left outer join has no records. ICriteria critera = session.CreateCriteria(typeof(Entity)); criteria.CreateCriteria("SubTable.Property", "Property", NHibernate.SqlCommand.JoinType.LeftOuterJoin); criteria.Add(Expression.Not(Expression.Eq("Prop...
ASP.NET ERROR : Keyword not supported: 'provider'
My app fails at this line of code: Dim objConnection As New SqlConnection(Application("ConnString")) My connection string is: "Server=testAITSQL;Database=SSCommerce;UID=PlanoWebApp;PWD=XXXXXXXX;" I googled this problem and the solution for it was having a malformed connection string where the "provider" was being specified when it ...
C++ library to discover sql database schema for multiple vendors?
Just wondering if anyone knows of a C++ library that provides a single interface for querying database schema (tables, fields, field types), and for a variety of vendors? I know DTL does this to some degree, although I haven't dug into the details of how it does it, or if it makes that info available external to itself. *ODBC is probab...
Compare Monday's data to previous Mondays in SQL Server
I am trying to figure out how to compare the current day's data to the same data from a week ago, 2 weeks, etc. Let's say I have a table called "Order" with 2 columns: Order table ----------- OrderID int identity OrderDate datetime If today, is Monday, I would like to be able to compare the number of orders from today to the previous...
SQL Server Reporting Services SQL server 2005 publish a report via web service
Imagine you have 2 applications Application A and Application B. Basically I would like to insert records that exist in Application A into Application B. Sql Server Reporting Server is available on Application A And I was wondering if it is possible to build a report that gathers the record that I need and then publish it as a web serv...
Is it possible to insert data into a MySQL view?
I made a MySQL view with 4 tables. Is it possible to insert data into the view and have MySQL automatically pass the data into the right table? ...
Entity Framework Overwriting ID Values
I am running into the same exact problem as asked in this question: Entity Framework, Foreign Keys, and EntityKeys where my Foreign ID key values are being reverted back to 0 before I am able to send the information to the database. This results in the error: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Sel...
Is it possible to only allow img tags in html comment post?
I have a comment form on my website which, at the moment I filter out all html and turn it into plain text and also replace bad words with funny words. I want to be able to allow users to post images. I couldn't see how to incorporate this to the comment page so have set it up on a separate page just dedicated to users posting images. Bu...
How can I combine sql aggregate functions with distinct more cleanly?
I've been running into the following case repeatedly lately, either I need to apply MAX() or SUM() to one column on a table, but I need DISTINCT sets of values for other columns. For example consider the following table and associated columns representing details of a login to StackOverflow. SoUserLogins (OpenIdToken, Name, IpAdress, L...
In python, how can I load a sqlite db completely to memory before connecting to it?
I have a 100 mega bytes sqlite db file that I would like to load to memory before performing sql queries. Is it possible to do that in python? Thanks ...