w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
mysql: How to group the result of GROUP BY in a query?
Popular and well described answer to your problem is right here: http://stackoverflow.com/a/612268/1803682 Specifically how do I select AggregateFunction(column) distinct on (other column) I will be happy to help more if you want to play with this in a SQL Fiddle and post it.

Categories : Mysql

MongoDB Group By query
You'd want to use the aggregation framework for this: db.Tracks.aggregate( [ { $match: { 'UserID': { $in: [ UserIDs ] } } }, { $group: { _id: '$UserID', max: { $max: '$_id' } }, { $sort: { $max: -1 } }, { $limit: 100 } ] ); First we match against the wanted UserIDs, then we group depending on UserID and also put the maximum _id value in the new max field. Then we sort by max descendently to get the highest max numbers first and then we limit by the top 100.

Categories : C#

Migrating to MongoDB: how to query GROUP BY + WHERE
There are examples of this all over the documentation, Google and this site. Some references: http://api.mongodb.org/python/current/examples/aggregation.html http://docs.mongodb.org/manual/reference/aggregation/group/ http://docs.mongodb.org/manual/reference/aggregation/sum/ And for some code: self.db.aggregate( # Lets find our records {"$match":{"Time":{"$gte":100,"$lte":1000}}}, # Now lets group on the name counting how many grouped documents we have {"$group":{"_id":"$name", "sum":{"$sum":1}}} )

Categories : Python

Rails with postgres - activerecord query: sort by column ASC, then group by column
This is what I did and its working. If there is a better way to go about these please post your answer: in my model: def self.fastest_per_car select('DISTINCT ON (car_id) *').order('car_id, time ASC').sort_by! {|ts| ts.time} end

Categories : SQL

select query to get those result where for every unique value of column 3, column 2 has bothval1 and val2
select t.* from your_table t inner join ( select id, min(type) as mint, max(type) as maxt from your_table where type in (6,7) group by id having count(distinct type) = 2 ) x on x.id = t.id and t.type in (x.maxt,x.mint) SQLFiddle demo

Categories : Mysql

Select and group MySQL column by certain word in result
You could group by the substring of browser, just using the first word in the column. The only issue I can think of is that Internet explorer will be returned as Internet. SELECT SUBSTRING_INDEX(browser,' ',1) as browser, COUNT( SUBSTRING_INDEX(browser,' ',1) ) as count FROM table_name GROUP BY SUBSTRING_INDEX(browser,' ',1)

Categories : PHP

How to format the result of a mongodb find query?
I have done this with aggregate function only, as explained in my blog post here For your case, this works db.temp.aggregate ( { $project : { name:"$name", created:"$details.varA.created", validity:"$details.varA.validity", modified:"$details.varA.modified" } } ); or db.temp.aggregate ({$project:{name:"$name",created:"$details.varA.created",validity:"$details.varA.validity",modified:"$details.varA.modified"}}); This is the sample run > db.temp.insert ({name:'sfdsfsdfsdfsdfsd', details: { varA : { created: "2013-06-13T12:43:25.853Z", validity: "2013-07-13T12:43:25.853Z", modified: "2013-06-13T12:43:25.853Z"}, varB : { created: "2013-06-13T12:43:25.853Z", validity: "2013-07-13T12:43:25.853Z", modified: "2013-06-13T12:43

Categories : Mongodb

remove last 100 elements from query result in mongodb
it = db.Usage.find({'Usage': "",'Rating': "", 'Average Ratings': ""})[1001:1500] to_rem = [] for doc in iter: to_rem.append( doc['_id'] ) try: db.Usage.remove( {'_id' :{ '$in': to_rem } }) except: print "Unexpected error:", sys.exc_info()[0]

Categories : Mongodb

Multiple Counts using single MONGODB group query
if (doc.interaction.twitter.mention_ids == $twitter_id) { prev.mentions++; } Assuming mention_ids is an array in this example, I can't imagine how this condition returns true if "the first value in array is matched". You're comparing an array to a string value (I assume that's what $twitter_id is). To search the mention_ids array in JavaScript, you could either iterate through it and compare values (ideally break-ing out of the iteration after the first match), or utilize the Array.indexOf() method. As a rule of thumb, MDN's JavaScript portal is an excellent reference. In addition to the core JavaScript API, you can find a list of other JavaScript functions at your disposal in map/reduce functions in the mapReduce command docs.

Categories : PHP

how to query group by and distinct with a limit in MongoDB PHP codeigniter?
First of all using group_by and distinct together doesn't make any sense. Either you are using group_by or distinct. If you want to do some kind of pagination for a grouped query you have to use map and reduce or the aggregation pipeline. In your case the aggregation would look like that. db.users.aggregate( { '$match' => { 'user' => 'ashok' } }, { '$group' => { '_id' => '$details.url' } }, { '$skip' => 0 }, { '$limit' => 10 } ) I am using this aggregation feature to display references at VersionEye. The aggregation feature allows to do grouping and paging on db level, that's why it's much faster then other ORM or pagination solutions.

Categories : PHP

how do i get last result in group by MySQL query (not the first)
Try using MAX(): SELECT lt_user, MAX(lt_time_stamp), lt_activity_operation FROM log_table GROUP BY lt_user, lt_activity_operation; See this SQLFiddle

Categories : Mysql

Getting correct result with Group By query in SQL Server
You need to find some way to aggregate the other columns. e.g. select Barkod, sum(kolicina) as Kolicina, Max(Kategorija) as Kategorija, Max(Artikal) as Artikal, Max(Opis) as Opis, Max(N_cena) as N_cena, Max(N_Iznos) as N_Iznos, Max(P_cena) as P_cena, Max(P_Iznos) as P_Iznos, Max(datum) as datum, Max(Golemina) as Golemina from stocks where Barkod = '555' group by Barkod This pattern would tend to suggest your data isn't properly normalized, though. Alternatively, if the non-summed columns always have identical values for the Barkod, you can just do: select Barkod, sum(kolicina) as Kolicina, Kategorija, Artikal, Opis, N_cena, N_Iznos, P_cena, P_Iznos, datum, Golemina from

Categories : SQL

Symfony2,Doctrine,MongoDb: Unable to pass query result to view
I work with doctrine this way: Controller: $products = $this->getDoctrine()->getRepository("AcmeStoreBundle:Product")->findProducts(); ProductRepository: class GalleryRepository extends EntityRepository { public function findProducts(){ return $this->getEntityManager()->getRepository("TLWEntitiesBundle:Product")->findAll(); } } The rest of the code seems ok.

Categories : Mongodb

MongoDB query for full text search and Geo returning wrong result
According to the 2.4 documentation, text indexes do not work with $near: Note You cannot combine the text command, which requires a special text index, with a query operator that requires a different type of special index. For example you cannot combine text with the $near operator.

Categories : Mongodb

MySQL Multiplied query result (LEFT JOIN, SUM, COUNT, GROUP BY)
You should manually count the records in a subquery for every user_id so you will get correct value. SELECT u.user_ID, u.Name, COALESCE(r.totalResult, 0) totalResult, COALESCE(n.totalNotes, 0) totalNotes, COALESCE(m.totalMessages, 0) totalMessages FROM users u LEFT JOIN ( SELECT user_ID, SUM(plus - minus) totalResult FROM Results GROUP BY user_ID ) r ON u.user_ID = r.user_ID LEFT JOIN ( SELECT user_ID, COUNT(*) totalNotes FROM Notes GROUP BY user_ID ) n ON u.user_ID = n.user_ID LEFT JOIN ( SELECT user_ID, COUNT(*) totalMessages FROM Messages GROUP BY user_ID ) m ON

Categories : PHP

How can I group by column value in a yii query?
One of Yii's strengths is it's ActiveRecord, so why not use it? Make your budget to a separate table (so you can generate a model from it). Reference it from your "datatable". CREATE TABLE budget ( id INTEGER PRIMARY KEY, name TEXT ); CREATE TABLE datatable( column1 TEXT, column2 TEXT, ... budget_id INTEGER, FOREIGN KEY(budget_id) REFERENCES budget(id) ); Next generate models with Gii, and now you can use your newly made relations like this: $budget = Budget::model()->findByAttributes( ["name"=>"budget2"] ); foreach( $budget->datatables as $dt ) { echo $dt->column1; echo $dt->column2; } (I know. Not the array you asked for. Sorry if I'm way off with this.)

Categories : Mysql

mongodb (compund) index on hashmap field results in inconsistent (count) query result
I can't quite explain why it would sometimes show a result, and sometimes not. This sounds like a bug so please make sure you're running the latest MongoDB version (2.4.5 at the time of writing this). If you can still reproduce it then, please file an issue at http://jira.mongodb.org/browse/SERVER — make sure to provide both the query, index creation and sample data set that shows the problem there.

Categories : Mongodb

performance of mysql query result using max, group by, temporary table and random row selection
You can do this with group_concat() and substring_index(): select wl_product_id, wl_size_id, price, substring_index(group_concat(aff_id order by rand()), ',', 1) as aff_id from t group by wl_product_id, wl_size_id, price; One note: This will convert the aff_id into a character representation. If you are using for join's afterwards, then you might want to convert it back to a number. EDIT: To get the information for the max price, then use a join to get that information: select t.* from (select wl_product_id, wl_size_id, price, substring_index(group_concat(aff_id order by rand()), ',', 1) as aff_id from t group by wl_product_id, wl_size_id, price ) t join (select wl_product_id, wl_size_id, max(price) as maxprice from t group by w

Categories : Mysql

add column to existing query with GROUP BY
If your column "cena" is a numeric price value, then you could perform an Aggregate Function on it. You could try to use the MAX(klisluz.cena) to get the maximum value, or SUM(..) to get a sum any other one that could apply to this column type. http://msdn.microsoft.com/en-us/library/ms173454.aspx

Categories : C#

mysql query group by where column distinct
It should look something like this SELECT COUNT(DISTINCT e.IP), count(e.id), count(c.id), e.Nid FROM tbl1 e LEFT JOIN tbl2 c ON e.Uid = c.Uid where e.at > '".$from."' AND e.at < '".$to."' GROUP BY e.Nid

Categories : Mysql

SELECT Row Values WHERE MAX() is Column Value In GROUP BY Query
SELECT Max(A) FROM MyTable Where B=(SELECT Max(A) FROM MyTable) update: SELECT * FROM MyTable Where B=(SELECT Max(A) FROM MyTable) update 2: SELECT DISTINCT A, B FROM MyTable Where A=(SELECT Max(A) FROM MyTable GROUP BY C) update 3: ok, I think I understand what you're looking for now.. How about this: SELECT * FROM MyTable Where A in (SELECT Max(A) FROM MyTable GROUP BY C)

Categories : SQL

sql query result returns asterisk "*" as column value
Use this update ##tempCLUnique set Total = U.unique_subs FROM ##tempCLUnique INNER JOIN ( select COUNT(distinct unique_subs)unique_subs from tbl_Cluster_Cumm_Unique_Subs )U ON u.cluster = ##tempCLUnique.cluster Change the join according to your use. Ashutosh

Categories : SQL

How to Update SQL Query's Result in a separate column
Your question is hard to parse. Here is my educated guess what you might be after: UPDATE clean_trips_byobu c SET dwell_time = COALESCE(u.diff, interval '-1 hour') FROM ( SELECT id, ref, start_time, end_time ,end_time - lead(start_time) OVER(PARTITION BY id ORDER BY start_time) AS diff FROM clean_trips_byobu ) u WHERE (c.id, c.ref, c.start_time, c.end_time) =(u.id, u.ref, u.start_time, u.end_time) AND c.dwell_time IS DISTINCT FROM COALESCE(u.diff, interval '-1 hour'); Use COALESCE() to replace NULL with a default interval. Use the query as subquery in your UPDATE. Join on a unique set of columns. If you already have values in diff, add the last line to prevent empty updates. But really you should have a simpler wa

Categories : SQL

Query for first result in a column - substring_index function - MySQL
You're using SUBSTRING_INDEX in the WHERE clause, which determines which rows to include. That's good, but you also need to use it in the SELECT clause, which determines which columns to include. Try this: SELECT last_name, first_name, SUBSTRING_INDEX(interests, ',' ,1) AS FirstInterestInList FROM ContactList WHERE last_name = 'Murphy' AND SUBSTRING_INDEX(interests, ',' ,1) = 'Pets';

Categories : Mysql

How to use the result of a SUBSTRING as column definition in an INSERT INTO query?
Dynamic SQL really is your only option and it can be safe and reliable if you use good practices like always using quotename(). The only other gotcha is if the query grows over 4000 characters in which case you should use nvarchar(max) and use exec (@query) instead of sp_executesql.

Categories : SQL

I have a query in mongodb and the reference key's are in hashmap list , I need to process a simple query using java in mongodb
When you post code, it helps if you indent it, so it is more readable. As I mentioned to you on another forum, you need to go back and review the Java collection classes, since you have multiple usage errors in the above code. Here are a few things you need to do to clean up your code: 1) You don't need to use the itkey iterator. Instead, use: for (String key : likey) and get rid of all the itkey.next calls. Your current code only processes every second element of the List. The other ones are printed out. 2) Your HashMap will map a key to a Boolean. Is that what you want? You said you want to count the number of non-zero values for the key. So, the line: Boolean listone = table.distinct(keys).contains(nullvalue); is almost certainly in error. 3) When you iterate over the Ha

Categories : Java

Insert data into table with result from another select query for one column
You have surrounded your subquery with quotes '..' wich make it becomes a string and been inserted as it is in your database, simply remove quotes and apply brackets instead INSERT INTO master_table (`Group_No`, `Group_Name`,`Order_No`) VALUES (6,'Purchase Account',(SELECT Order_no FROM acc_group_master where Group_No=1))

Categories : Mysql

sql query to select columns by condition on specific column(with out aggregate function) with group by
The query below should roughly do what you need. So in the first select it gets the empID in combination with the latest reg_date. THe second select gets all the data in the table and in the final select we join the 2 datasets on the criteria listed which should get you the empID, the max regdate and the values for data1, data2, data3 that correspond. ;with GetDataSet1 as ( SELECT EmpId,MAX(reg_date) "MaxRegDate" FROM Employee GROUP BY EmpId ) ,GetDataSet2 as ( SELECT EmpId,data1,data2,data3,reg_date FROM Employee ) select * FROM GetDataSet1 a JOIN GetDataSet2 b on a.EmpID = b.EmpID AND a.MaxRegDate = b.reg_date

Categories : SQL

Is there any way to aggregate and/or concatenate the values of a column as comma separated items in a Group By query?
You can use FOR XML PATH to concatenate the values into a single row: Select t1.TID, Min(t1.StartId) As MinSID, Max(t1.EndId) As MaxSID, Min(t1.StartTime) As MinStartTime, Max(t1.EndTime) As MaxStartTime, STUFF((SELECT ', ' + t2.ResName FROM Item t2 where t1.TID = t2.TID FOR XML PATH ('')) , 1, 1, '') AS TIDNames From Item t1 Group By t1.TID; See SQL Fiddle with Demo. If the data you are querying is from another query, then you can use a CTE: ;with cte as ( select * from yourqueryHere ) select t1.TID, Min(t1.StartId) As MinSID, Max(t1.EndId) As MaxSID, Min(t1.StartTime) As MinStartTime, Max(t1.EndTime) As MaxStartTime, STUFF((SELECT ', ' + t2.ResName FROM cte t2 where t1.TID = t2.TID F

Categories : Sql Server

MongoDB (Node JS), how to correctly add calculated fields to a query result, where the calculated field uses a passed variable
You ask how to do the calculation using the aggregation framework. In general the aggregation framework performs better than map-reduce. You can find documentation on the Aggregation Framework here: http://docs.mongodb.org/manual/aggregation/. I understand that the calculation you want is, given a user, to find all feeds where that user is contained in the openedBy array, and then find the number of distinct friends of that user that are contained in those openedBy arrays. Do I have that correct? Aggregation, like map-reduce, only operates on one collection at a time, so the first step is to obtain the list of friends for the user from the users collection, for example: friends = db.users.findOne({_id:user}).friends Then we can perform the following aggregation on the feeds collection

Categories : Node Js

MongoDB Mapreduce: query/restriction/filter on result of mapreduce?
You can add to your reduce function function(key, values) { var res = values[0]; for(var i=1; i<values.length; i++) { if(values[i].version >res.version) { res = values[i]; } } if ('someAttr' in res && res.someAttr == "a") return res; } Or if you want to check for the latest version between just the ones that have someAttr set to 'a' you can do function(key, values) { var res = {version : 0} for(var i=0; i<values.length; i++) { if('someAttr' in res && res.someAttr == "a" && values[i].version >res.version) { res = values[i]; } } return res; }

Categories : Mongodb

Need to flatten column to a single row per group with a sum of the column values for each group
From what I can see, I would move the SUM from the inside, to the outside, remove the last 3 items from the GROUP BY and remove the HAVING statement Something like SELECT CASE a.productid WHEN 6 THEN a.productid + space(2) + a.cusotmerid + p.progcode+ w.level WHEN 7 THEN a.productid + space(1) + a.cusotmerid + p.progcode+ w.level ELSE a.productid + space(3) + a.cusotmerid + p.progcode + w.level END AS aonBom, sum(CASE WHEN (s.sku IN('80-53-0000', '80-52-TTSB') or s.sku BETWEEN '90-00-0000' AND '90-00-9999') THEN s.retail END) as Fees FROM l INNER JOIN item ON l.product = i.prime INNER JOIN item AS s ON l.compon =s.prime INNER JOIN a ON i.prime = a.product INNER JOIN p ON a.pro

Categories : Sql Server

Translating MongoDB query to a MongoDB java driver query
I haven't checked the syntax. Also I don't know if your query works or not but here's my try. //unwind DBObject unwind = new BasicDBObject("$unwind", "$scorearray"); // Now the $group operation DBObject groupFields = new BasicDBObject("player", "$player"); groupFields.put("venue", "$scorearray.venue")); groupFields.put("score", "$scorearray.score")); DBObject group = new BasicDBObject("$group", new BasicDBObject("_id", groupFields)); //sort DBObject sort = new BasicDBObject("$sort", new BasicDBObject("_id.score":1)); //secondgroup DBObject secondGroupFields = new BasicDBObject("_id", "$_id.player") secondGroupFields.put("maxScore", new BasicDBObject("$last":"$_id.score")); secondGroupFi

Categories : Mongodb

Group rows by value but only displaying the newest row in group result
Try this one: SELECT user_id,count(user_id),timestamp from (SELECT DISTINCT user_id, COUNT(user_id),timestamp FROM applications WHERE approved =0 ORDER BY timestamp DESC) AS temp;

Categories : Mysql

Query not returning result (but result exists and query works in different project)
The error says that you have disposed the DbContext object. inspect your controller code. Maybe it's cause of creating the controller using scaffolding. If so, inspect the controller code to find somthing like the following: protected override void Dispose(bool disposing) { db.Dispose(); base.Dispose(disposing); } Either remove it(not recommended) or put call your DbContext object before it. Or maybe you dispose your object manually, or declaring your object by a using() clause, etc. Anyway, this error occurs because you call the object after disposing it. Inspect your code... Update: Another guess: It seems that you are using navigation properties in the query. If so, in this case, you should include any NavPr you need. Try something like this: geographic =

Categories : Asp Net Mvc

R- Perform operations on column and place result in a different column, with the operation specified by the output column's name
It seems strange that you would store your operations in your column names, but I suppose it is possible to achieve: As always, sample data helps. ## Creating some sample data mydf <- setNames(data.frame(matrix(1:9, ncol = 3)), c("L1", "L2", "L3")) ## The operation you want to do... morecols <- c( combn(names(mydf), 2, FUN=function(x) paste(x, collapse = "+")), combn(names(mydf), 2, FUN=function(x) paste(x, collapse = "-")) ) ## THE FINAL SAMPLE DATA mydf[, morecols] <- NA mydf # L1 L2 L3 L1+L2 L1+L3 L2+L3 L1-L2 L1-L3 L2-L3 # 1 1 4 7 NA NA NA NA NA NA # 2 2 5 8 NA NA NA NA NA NA # 3 3 6 9 NA NA NA NA NA NA One solution could be to use eval(parse(...)) within lapply to perform the calculati

Categories : R

query based on matching elements in DBRef list for mongodb using spring-data-mongodb
Querying for one element on an array is exactly like query for a field equality. You could read the MongoDB documentation here. So your query will be: new Query(Criteria.where("users.$id").is(new ObjectId(userId)))

Categories : Mongodb

MYSQL condense group by two columns into group by one column with breakdown
Ugly, untested, but may just do what you want: SELECT type, SUM(color_count), GROUP_CONCAT(CONCAT(color_count, ' ', colour) SEPARATOR ', ') AS colour_breakdown FROM ( SELECT type, colour, COUNT(colour) AS colour_count FROM ocean GROUP BY type, colour ) AS subt GROUP BY type

Categories : Mysql

sql query - how to include columns in a group by query which are not in the "group by"?
In SQL, GROUP BY actually need an aggregate function for the SQL parser to understand what you are trying to do. I suggest you re-code the SQL to something like this ... select Pat.PatientName, COUNT(med.MedicineId), from medicinemaster as med join PatientMaster as Pat on med.PatientId = Pat.PatientId group by Pat.PatientName Obviously that is not what you wanted, because the above SQL will now show you the PatientName and their MedicineID counts. If you want to see the actual IDs, then you need something like this ... select Pat.PatientName, med.MedicineId, COUNT(*) as RowCount from medicinemaster as med join PatientMaster as Pat on med.PatientId = Pat.PatientId group by Pat.PatientName, med.MedicineId And the RowCount column will show you how many times the

Categories : SQL

SQL Query: Group Time into timeslices then group by loginname then count distinct loginnames per timeslice
select to_timestamp(extract('epoch' from starttime)::int / 600 * 600) AS timeslice, count(distinct loginname) total from request where starttime >= '2013-06-11 00:00:00' and starttime < '2013-06-11 01:00:00' group by timeslice order by timeslice asc

Categories : SQL



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.