I need to run a query in Wordpress to get how many posts there are per month including zeros. My query right now returns the first part of what I am trying to return: select distinct date_format(post_date, '%y') "year", date_format(post_date, '%b') "month", from wp_posts where post_type = 'post' and post_status = 'publish' group by dat...
Ok, I have over a million records and I need to select all information from the database where the count of a column is greater or equal to 10. I know that using having I must use group by and using where I cannot use aggregate functions with operators so what I want in pseudo code is Select column1,column2,column3 From MYdatabase Wher...
I want to make an order by stuff using a part of some column.For example,some records of the column user look as below vip1,vip2,vip21,vip10,vip100 If I do a order by user,the order would be vip1,vip10,vip100,vip2,vip21 Then how can I make the result as follows vip1,vip2,vip10,vip21,vip100 Thanks. ...
I'm playing around with a little web app in web.py, and am setting up a url to return a JSON object. What's the best way to convert a SQL table to JSON using python? ...
I have an online form, whereby each entry adds the data as xml to an xml column in SQL. ApplicationID(uniqueidentifier) | DateModified | ApplicationForm(XML) What I need to do is perform a select query that will grab all the ApplicationForm xml values, and concatenate them together to form one result, e.g. Row1: <ApplicationForm type...
lets say i have this table user|group|acceptance 1 | a | -1 2 | a | 2 3 | b | 1 4 | b | 2 5 | b | 2 6 | c | -1 how do i get count how many users in each group have acceptance not -1 but still list the group having 0 count so result would be like group | count a | 1 b | 3 c | 0 thanks for the he...
The output below is from Oracle; where it generates "create table" statements using a supplied package. I feed them into the python diff tool HtmlDiff which uses difflib under the covers. Each table is followed by a number of "alter table add constraint" commands that add the various constraints. The problem is that there is no explicit...
I am tracking clicks over three time periods: the past day, past week and past month. To do this, I have three tables: An hourly table, with columns link_id, two other attributes, and hour_1 to hour_24, together with a computed column giving the sum A weekday table, with columns click_id, two other attributes, and day_1 to day_7, toge...
Hi everyone, I have read through some excellent responses already dealing with SQL XML output posts, although what I need might not be a) best to do within a stored proc and b) not possible within a stored proc. My question is this: I have a simple table; col1, col2, col3, col4 .. for the purpose of an example, they are all just varch...
I have a column, num, and I'm trying to update the column such that num = num / MAX(num). Now the thing is that MAX(num) should remain static throughout the update.. it should be the MAX prior to the update. I was wondering whether this was possible to do in one SQL statement. The obvious solution otherwise that I'm currently using it v...
Hi I want to copy N number of records within a table which is easy with a INSERT SELECT however after the insert I want a list showing oldId and newId. So lets say I have a SELECT statement which returns: ID | Name 3 | Test A 4 | Test B 5 | Test C If I do a SELECT INTO from this it inserts 3 new PKs. I want a list showing the new ...
Hello, I was wondering what the "best" way to verify the structure of my database is with SQLite in Qt / C++. I'm using SQLite so there is a file which contains my database, and I want to make sure that, when launching the program, the database is structured the way it should be- i.e., it has X tables each with their own Y columns, app...
I'm using the Web SQL API to store some information on a page; I'd like to save the state of the page when the user closes the page or navigates away, thus, I start with: window.onunload = function () { db.transaction(function (tx) { executeSql("INSERT INTO State (foo) VALUES (?)", [foo]); }); }; However, this is asynchronous ...
Inspired by this question on Meta, I wrote two queries on the Stack Exchange Data Explorer, one that counts the total number of Questions Asked by Month on SO and another that counts the Bounties Awarded by Month. How can I combine them so that I have the output in one query? I'd like to see Year, Month, Questions, Bounties, and Amount...
Hi All, I need to store a set of values e.g. A, B, C under say country column of a SQL table. Coould you let me know if there is any data type that is able to store a set of values in a SQL table column? Thanks in advance. ...
It happened that all projects during the last 10 years were with Oracle as a database server. Now I'm starting on a side project on my own with MySQL (the latest stable). Are there any gotchas (things that do not work as expected by an Oracle user)? Anything to do with transaction management, locking, isolation levels, indexes, that ki...
I'm trying to write a linq-to-sql query using || that behaves the same way as the OR in SQL when combined with a LIKE/Contains. SQL: SELECT * FROM Users WHERE GroupNumber = 'A123456' OR (FirstName like 'Bob%' AND LastName like 'Smith%') This will result in everyone with a name like "Bob Smith" as well as everyone with a GroupNumber e...
i have a "TEXT" type column in a sql database. I queried it with ResultSet rs=db.execSQL(query); String s=rs.getString("text_field"); creates and error. how should i use this text column in my java? ...
I have two tables invoices and receipts i have to show the alternate data of invoices and receipts in datagrid; condition is that if Date column in INVOICE table matches with invoice columns in RECEIPTS table then the corresponding row of RECEIPTS table should come after INVOICES table row if there is no match then INVOICES table n...
I know this is a big question and it's not a yes or no answer but we develop web apps and are looking into using MongoDB for our persistence solution. Combining MongoDB with NoRM for object storage. What I want to ask is what pitfalls have you experienced with switching from SQL to mongo? When is mongo simply not the right solution and ...