group-by

group by sql statement

so i got this statement, which works fine: SELECT MAX(patient_history_date_bio) AS med_date, medication_name FROM biological WHERE patient_id = 12) GROUP BY medication_name but, I would like to have the corresponding medication_dose also. So I type this up SELECT MAX(patient_history_date_bio) AS med_date, medication_name, ...

How to Group Multiple Items using GroupBy()?

Consider the following simplified objects and relationships: public class Job{ int JobId; String name; String status; Discipline disc; WorkCategory workCat; } public class Discipline { int DisciplineId; String DisciplineName; } public class Workcategory{ int WorkCategoryId; String WorkCategoryName; } public Class ...

How to make a total and Grouping a report

Using Crystal Report 8.5 Generating a report from table1 My Crystal Report Output ID Name Date Totaltime 001 Raja 23/02/2009 08:00:00 001 Raja 24/02/2009 10:00:00 001 Raja 25/02/2009 09:00:00 001 Raja 26/02/2009 11:00:00 001 Raja 27/02/2009 07:00:00 002 Ravi 23/02/2009 11:00:00 002 Ravi 24/02/2009 10:00:00 002 Ravi 25/02/2009...

SQL request with "Group by" and "max" and "join" ?

Here is a sample of my DATA: CLIENT_ATTRIBUT : ID_CLIENT | DATE_CLIENT | ATTRIBUT ----------+-------------+--------- 000000001 | 2010:03:01 | 0000010 ----------+-------------+--------- 000000001 | 2010:02:16 | 0000010 ----------+-------------+--------- 000000001 | 2010:03:04 | 0000011 ----------+-------------+--------- 000000002 | 2...

Get multiple GROUP BY results per group, or use separate concatenated table

I am working on an auction web application. Now i have a table with bids, and from this table i want to select the last 10 bids per auction. Now I know I can get the last bid by using something like: SELECT bids.id FROM bids WHERE * GROUP BY bids.id ORDER BY bids.created Now I have read that setting an amount for the GROUP BY results ...

SQL Query improvement - Select with max and groupby

Problem Given the following two tables, I'd like to select all Ids for Posts that have their most recent (i.e. last) comment made in the given time span (e.g. Feb 2010). The result of the query should only return Post ID 1, since the most recent comment for Post ID 2 is outside the range of the time span filter. Question I've c...

How to use SQL to do a group by with different dates?

Lets say I have a table with the following columns: Qty, INTEGER SaleDate, DATETIME I would now like to see this result: Sold in the last 7 days | Sold in last 14 days ----------------------------------------------------- 10 | 20 I can use a where clause to use between, however how would I get the qty sold for...

SQL Query to return 24 hour, hourly count even when no values exist?

I've written a query that groups the number of rows per hour, based on a given date range. SELECT CONVERT(VARCHAR(8),TransactionTime,101) + ' ' + CONVERT(VARCHAR(2),TransactionTime,108) as TDate, COUNT(TransactionID) AS TotalHourlyTransactions FROM MyTransactions WITH (NOLOCK) WHERE TransactionTime BETWEEN CAST(@StartDate A...

How to apply a SUM operation without grouping the results in SQL?

I have a table like this one: +----+---------+----------+ | id | group | value | +----+---------+----------+ | 1 | GROUP A | 0.641028 | | 2 | GROUP B | 0.946927 | | 3 | GROUP A | 0.811552 | | 4 | GROUP C | 0.216978 | | 5 | GROUP A | 0.650232 | +----+---------+----------+ If I perform the following query: SELECT `id`, S...

How to: Using NHibernate ICriteria for grouping, fetching associations and using T-SQL functions

Hi! I want to create the following T-SQL statement: SELECT SUM (sa.Amount) as 'SumAmount', SUM(sa.Cost) as 'SumCost', gg.[Description] as 'Goodsgroup', Month(sa.[Date]) as 'Month' FROM SalesmanArticle sa INNER JOIN Article a ON a.ArticleId = sa.ArticleId INNER JOIN GoodsGroup gg ON gg.GoodsGroupId = a.GoodsGr...

Find distinct count of group by user

I want to solve this issue in query @ for Ex : id ip 1 1.2.3.3 2 1.2.3.3 3 1.23.42.2 4 4.4.2.1 I am looking for ip count ids 1.2.3.3 2 1,2 1.23.42.2 1 3 4.4.2.1 1 4 I have tried with query - select count(id) ,ip , id from table group by ip ; it not working . how to solve this ....

MySQL group by summary

hey in my members table i would like a summary of all different people in "locations" 1 - 7 of how many people are online and offline. SELECT location, COUNT(*) FROM members GROUP BY location; that returns: 1 10 2 5 3 4 4 12 5 6 6 3 7 19 I would like a COUNT for members with a status of 0 (offline) and a status of 1 ...

How can I put rows of MySQL data under the appropriate titles using PHP?

I have the following MySQL table structure: num field company phone website 1 Gas abcd 123456789 abcd.com 2 Water efgh 987654321 efgh.com 3 Water ijkl 321654987 ijkl.com 4 Heat mnop 987654321 mnop.com 5 Gas qrst 123789654 qrst.com ... Is it possible with PHP (maybe using some mixture of GROUP_BY and ...

MySQL timestamp and GROUP BY with two tables

I have a table with 3 fields: ID (not unique, not primary key) timestamp marker I want to get the last entry for every distinct ID, but when I doSELECT ID, max(timestamp) from table GROUP BY IDthe marker field is wrong. I have another table with the following fields: ID (unique, primary key) lat lng I would like to perform the s...

what is the difference between WHERE and HAVING

Possible Duplicate: SQL: Whats the difference between HAVING and WHERE? i am learning sql syntax and i can't understand this. the second half of the question is a much more technical one. what is actually happening behind the scenes of the database between WHERE and HAVING? which one uses more resources? are they same algori...

SQL COUNT of COUNT

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 COUNT and GROUP BY), but I then want to be able to count that. So, I would like to see how many items appear twice, three times, etc. So for...

Group by with ActiveRecord in Rails

Hello, I have a the following table with rows: =============================================== id| name | group1 | group2 | group3 | group4 | =============================================== 1 | Bob | 1 | 0 | 0 | 1 | =============================================== 2 | Eric | 0 | 1 | 0 | 1 | ==...

linq2nhibernate group by and orderby count() getting null reference

Hello, I'm trying to execute this linq2nhibernate query: from e in endContratoRepository.GetAll() where e.Contrato.idContrato == contrato.idContrato && e.Volume.idVolume == contratoVolume.Volume.idVolume && ...

Django: Group by?

I'm looking for something like the following: previous_invoices = Invoice.objects.filter(is_open=False).order_by('-created').group_by('user') (group_by() doesn't exist) This would find the most recently closed invoice for each user. This aggregation API seems to let you do stuff like this for counts and sums, but I don't need a coun...

What are the Ruby equivalent of Python itertools, esp. combinations/permutations/groupby?

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 combinations('ABCD', 2) --> AB AC AD BC BD CD [list(g) for k, g in groupby('AAAABBBCCD')] --> AAAA BBB CC D What are the equivalent in Ruby? By eq...