group-by

SQL - ID is lost after performing MIN then GROUP BY

To simplify, if I had a table called 'employee': id INT NOT NULL PRIMARY KEY, salary FLOAT, department VARCHAR(255) I want to perform and query where I retrieve the minimum salary in each department. So my query is something like this: SELECT employee.ID, MIN(employee.salary), employee.department FROM employee GROUP BY employee.depar...

How must the mysql query be like to achieve the shown result?

Say, I have two tables like these: Table group Table user +----+-----------+ +----+----------+------+----------+ | id | groupname | | id | username | rank | group_id | +----+-----------+ +----+----------+------+----------+ | 1 | Friends | | 1 | Frank | 1 | 1 | | 2 | Family | | 2 | Mike | 3 | ...

MySQL GROUP BY performance issue

This is the query I'm performing (without some Joins that are not relevant): SELECT a.*, c.id FROM a LEFT OUTER JOIN b ON a.id = b.id_anunciante LEFT OUTER JOIN c ON c.id = b.id_rubro GROUP BY a.id Each row of "a" is linked with 1 to 5 rows in "b". The problem is that GROUP BY has performance issues (it takes 10x or more using GROUP ...

How to order, group, order with mySQL

Here is a simplified version of my table tbl_records -title -created -views I am wondering how I can make a query where they are grouped by title, but the record that is returned for each group is the most recently created. I then will order it by views. One way I guess is to do a sub query and order it by created and then group it b...

SQLite3 Doesn't Produce an Aggregation Error on Malformed/Indeterminate Queries?

SQLite behaves differently when dealing with aggregation than many other RDBMS's. Consider the following table and values: create table foo (a int, b int); insert into foo (a, b) values (1, 10); insert into foo (a, b) values (2, 11); insert into foo (a, b) values (3, 12); If I query it thus: select a, group_concat(b) from foo; Norm...

How do I count occurrences by day in SQL?

Newbie SQL question here --> I've got an Occurrences table that contains a row for each time a user did something. A user can do the thing multiple times per day. It looks like this: Date Username ------ -------- 1/1/9 User1 1/1/9 User1 1/1/9 User2 1/2/9 User1 1/2/9 User3 1/3/9 User1 1/3/9 User1 1/...

SUM() Problem in Paradox Database

Hello All, I'm currently working with a paradox database that was implemented before I started working at my current job at an insurance firm. Long story short is that when I am trying to to compile a query of the debit/credit balances of all the active clients, it gives me a different balance per client if I do a query for each indivi...

using group_concat in PHPMYADMIN will show the result as [BLOB - 3B]

I have a query which uses the GROUP_CONCAT of mysql on an integer field. I am using PHPMYADMIN to develop this query. My problem that instead of showing 1,2 which is the result of the concatenated field, I get [BLOB - 3B]. Query is SELECT rec_id,GROUP_CONCAT(user_id) FROM t1 GROUP BY rec_id (both fields are unsigned int, both are n...

JSON VIEW using GROUP_CONCAT question

Hey DBAs and overall smart dudes. I have a question for you. We use MySQL VIEWs to format our data as JSON when it's returned (as a BLOB), which is convenient (though not particularly nice on performance, but we already know this). But, I can't seem to get a particular query working right now (each row contains NULL when it should con...

Two level group by, order by

what is the linq equivalent of the following: SELECT Mytable.A, MyTable.B, SUM(MyTable.C) FROM MyTable GROUP BY Mytable.A, MyTable.B ORDER BY Mytable.A, MyTable.B This is trivial in SQL, but seems to be very difficult in LINQ. What am I missing? ...

Group and filter XML data with XSL

I have the following XML code: <root> <options> <companies> <company url="http://www.brown.com"&gt;Brown LLC</company> <company url="http://www.yellow.com"&gt;Yellow LLC</company> <company url="http://www.black.com"&gt;Black LLC</company> <company url="http://www.bourdeaux.com"...

Group By either column using C# LINQ

I have a set of Data with columns such as below OffName,RO1,RO2,RO3 To explain further i use sample data as below: OffName RO1 RO2 RO3 A John Jack Rob B Earl John Carl C Rob Chris Kenny D Rodney Carl Jacob RO stands for Reporting Officer. Each Officer reports to upto 3 RO's.i need to make...

How to group elements by attribute values using XQuery on SQL Server?

Let's say I have a table in SQL Server which contains the results of a query with an inner join. The following XQuery: select @code.query ( 'for $s in /root/row return <Foo Language="{data($s/@lang)}" Method="{data($s/@method)}" Returns="{data($s/@returnType)}"> <Bar ReferencedBy="{data($s/@callers)}" Static="{data($s/@static)}" /> </...

SQL Server Group By which counts occurrences of a score

Hi, This might be a bit difficult to explain but I have two columns in my SQL server database which I have simplified... Items ID itemName voteCount score Votes itemID score So, basically I am storing every vote that is placed in one table, but also a count of the number of votes for each item in the item table along with it's averag...

MySQL - Counting rows and left join problem

I have 2 tables, campaigns and campaign_codes: campaigns: id, partner_id, status campaign_codes: id, code, status I want to get a count of all campaign_codes for all campaigns WHERE campaign_codes.status equals 0 OR where there are no campaign_codes records for a campaign. I have the following SQL, but of course the WHERE statement e...

mysql optimization - display 10 most recent records, but also identify duplicate rows

I am new to mysql and I have been pulling my hair out about this problem for days. I need to improve/optimize this query so that it runs faster - right now its taking over 5 seconds. Here is the query: SELECT SQL_NO_CACHE COUNT(*) as multiple, a.*,b.* FROM announcements as a INNER JOIN stores as s ON a.username=s.username ...

Remove bias from the monthly totals

In order to remove the bias introduced by the differences in the number of days in the months and years (in case of leap years), from monthly total comparisons of arbitrary quantities and assuming, for example, a table named My_Table with a datetime column named order_date, and an integer one named revenue, I use the following query to g...

Any reason for GROUP BY clause without aggregation function?

I'm (thoroughly) learning SQL at the moment and came across the GROUP BY clause. GROUP BY aggregates or groups the resultset according to the argument(s) you give it. If you use this clause in a query you can then perform aggregate functions on the resultset to find statistical information on the resultset like finding averages (AVG()) ...

MySQL selecting and grouping rows if one after another

Hello, I'm developing an auctions application in which bids are held in a table with the following format: id | user_id | auction_id | placed_at I'd like to group the bids by user id and select their counts, but only if the user ids appear one after another. The bids are ordered by placed_at descending. Here's what I mean: 1 | 1 | 1...

Join, Group By and Sum in MySQL

Hello, I have the following three tables in a MySQL database in order to give ratings to comments of users: Users: id name ----------- 1 Smith 2 Brown Comments: id user_id post_id comment ----------------------------------- 1 2 1 Test 1 2 1 1 Test 2 3 1 1 ...