Questions tagged [group-by]
GROUP BY is a command in the SQL relational database standard for collapsing a group of rows that share common field value(s) into a single row. Aggregate functions can be performed on other fields in the group, such as SUM() or AVG(), to collate related data into a single value.
In SQL, using the table:
mysql> select * from sentGifts;
| sentID | whenSent | fromID | toID | trytryWhen | ...
In SQL, if we use Group By without Count(*) or Sum(), etc, then the result is as follows:
mysql> select * from sentGifts;
A SQL statement can give a list of the most popular gifts that are sent in a Social application, all the way to the ones that are sent 1, or 2 times, but it won't include the Zeros.
I think the same ...
I have a mysql select query that has a groupBy.
I want to count all the records after the group by statement.
Is there a way for this directly from mysql ?
Is is possible to select a datetime field from a MySQL table and group by the date only?
I'm trying to output a list of events that happen at multiple times, grouped by the date it happened on.
I have a quite large table storing words contained in email messages
mysql> explain t_message_words;
| Field | Type ...
Hello i have a products table that contains normal products and configurable product
It has a basic stucture of:
configurable ('yes', 'no')
Normal products have ...
This is a situation I'm generally facing while writing SQL queries. I think that writing the whole column (e.g. long case expressions, sum functions with long parameters) instead of aliases in GROUP ...
I have a GroupBy that I groups all elements. I can see the items are there in LinqPad but can't find a way to get the count.
Here is what I have so far:
SurveyResponses.Where( q => q.QuestionId ==...
I'm having trouble with Microsoft Access 2003, it's complaining about this statement:
group by cardnr
having max(time+date) = (time+date) and ...
I'm having the following two tables...
Table : room_type
type_id type_name no_of_rooms max_guests rate
1 Type 1 15 2 1254
2 Type 2 ...
Can anyonen help with Teradata?
I want to create a query that is a standard
where Column = Something
but has a group by time period done by 5 minute time intervals ...
Here it's my problem I've a list of the following measure :
src1 dst2 24th december 2009
src1 dst3 22th december 2009
src1 dst2 18th december 2009
I would like to have just the latest ...
Is there a simple way to LIMIT the GROUP BY results to the top 2. The following query returns all the results. Using 'LIMIT 2' reduces the overall list to the top 2 entries only.
fail statement:Error: ORA-00979: not a GROUP BY expression
select org_division.name , org_department.name , org_surveylog.division_code as divisionCode,org_surveylog.department_code as ...
Consider the following DB table:
Now, my goal is to retrieve a list of numbers c, for which holds that each number in this list has at least ...
SELECT MAX(some_field) FROM table_A GROUP BY another_field
This only gets the max value of the 'some_field'; I want to get the whole record which contains the MAX(some_field).
I have a 2 dimensional array forming a table:
[black][10 ][square ]
[black][10 ][circle ]
[red ][05 ][triangle]
[red ][04 ][...
I'm getting average prices by week on 7 million rows, it's taking around 30 seconds to get the job done.
This is the simple query:
SELECT AVG(price) as price, yearWEEK(FROM_UNIXTIME(timelog)) as ...
SELECT SUM(a.Clicks) AS Clicks, SUM(b.NoOfUsers) Users, c.WEEK_NUM, b2.ALL_TASKS FROM
(SELECT SUM(CLICK_CNT) AS Clicks, TO_CHAR(RQST_DT,'YYYY-MM-DD') AS DATEE FROM PER_DAY_USAGE GROUP ...
i have a table with the columns such id, tid, companyid, ttype etc..
the id may be same for many companyid but unique within the companyid and
tid is always unique and
i want to calculate the total ...
Table A links to Table b from the tableBID found in both tables.
If I want to find the row in Table A, which has the highest ...
I've a table with a datetime (format: 'Y-m-d H:i:s') 'created' field and 'amount' (integer) field in each row. Now I want to find out month wise total 'amount' in last year. How can I do this?
I have the following tables:
posts (post_id, content, etc)
comments (comment_id, post_id, content, etc)
posts_categories (post_category_id, post_id, category_id)
and this query:
help me please with this simple E-sql query:
var qStr = "SELECT SqlServer.Month(o.DatePaid) as month, SqlServer.Sum(o.PaidMoney) as PaidMoney FROM XACCModel.OrdersIncomes as o group by SqlServer....
i've got a table like that :
Article Number Last Voucher Number Last Voucher Date
0557934 519048 04/02/2005
0557934 519067 04/02/2005
I have the following sample data:
Id Name Quantity
1 Red 1
2 Red 3
3 Blue 1
4 Red 1
5 Yellow 3
So for this example, there are a total of 5 Red, ...
Here is my data structure
alt text http://luvboy.co.cc/images/db.JPG
when i try this sql
select rec_id, customer_id, dc_number, balance
group by ...
I am writing a stored procedure for displaying month and year. It is working, but it is not returning the rows in the desired order.
ALTER procedure [dbo].[audioblog_getarchivedates]
I have a table like this:
Currently I'm grouping by the id and concatenating ...
the data like this:
i want get result like this:
who can tell me how to do using DB4objects
Hi i've wrote a query that works:
SELECT `comments`.* FROM `comments`
RIGHT JOIN (SELECT MAX( id ) AS id, core_id, topic_id
FROM comments GROUP BY core_id, topic_id order by id desc) comm
I'm building a software for timereporting
I have a Dictionary<string, Dictionary<string, double>>. The key in the main dictionary is a users name and their value is a dictionary of .
I have a simple user production report where daily quotas are tracked. The sql returns a weeks worth of data for all users and for each day it tracks their totals. The problem is if they are out for a ...
Right now I have the following query:
SELECT name, COUNT(name), time, price, ip, SUM(price)
WHERE time >= $yesterday
AND time <$today GROUP BY name
And what I'd like to ...
I have an example database, it contains tables for Movies, People and Credits. The Movie table contains a Title and an Id. The People table contains a Name and an Id. The Credits table relates ...
I've got MySQL table
CREATE TABLE cms_webstat (
ID int NOT NULL auto_increment PRIMARY KEY,
TIMESTAMP_X timestamp DEFAULT CURRENT_TIMESTAMP,
# ... some other fields ...
which contains ...
i have a blog application were Post belongsTo Category and Category hasMany Post
Post can have a Category or not - in latter case NULL value is present in Post.category_id field.
Now i would like to ...
I have a table like below:
I need to a to assign requests in a sequential fashion(those who has mininum number of requests).
This is for http://cssfingerprint.com
I collect timing stats about how fast the different methods I use perform on different browsers, etc., so that I can optimize the scraping speed. Separately, I ...
I have a criteria query:
.Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.location))
I ended up with this horrible code below, I can't get a better result now.
What is a better way of doing that?
It's about this part of my database:
A Patient has a Subscription to multiple ...
I have data in 2 tables, and I want to create a report.
tableAID (primary key)
tableBID (primary key)
tableAID (foreign key, references Table A)
There is much more ...
Hello i have a question on picking random entries from a database. I have 4 tables, products, bids and autobids, and users.
is possible do a case in a group by?
similar to this:
select * from table
CASE WHEN @Attivita=0 THEN (RANK() OVER (GROUP BY Nome,AccountID,Matricola DESC))
Python's itertools module provides a lots of goodies with respect to processing an iterable/iterator by use of generators. For example,
permutations(range(3)) --> 012 021 102 120 201 210
I'm looking for something like the following:
previous_invoices = Invoice.objects.filter(is_open=False)
I have a the following table with rows:
id| name | group1 | group2 | group3 | group4 |
1 | Bob | 1 ...
I have some data I am querying. The table is composed of two columns - a unique ID, and a value. I would like to count the number of times each unique value appears (which can easily be done with a ...
I understand the point of GROUP BY x
But how does GROUP BY x, y work, and what does it mean?