group-by

Get Max() record from table by group

Hi, 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 0557934 528630 09/29/2005 0557934 528631 09/29/2005 0557934 529374 10/13/2...

Entity Sql Group By problem, please help

Hello, 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.Month(o.DatePaid)"; Here's what I have. I have simple Entity called OrdersIncomes with ID,PaidMoney,DatePaid,Order_ID properties I ...

MySQL GROUP BY with three tables

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: SELECT `p`.* , COUNT(comments.comment_id) AS cmts , posts_categories.* , comments.* FROM `posts` AS `p` LEFT JOIN `posts_categories` ...

how to group MySql rows based on month?

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? EDIT I made an edit to clarify the actual problem. so basically I want to know total 'amount' in each month, but only for the last year. EDIT2 ...

sql - getting the id from a row based on a group by

Table A tableAID tableBID grade Table B tableBID name description 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 grade, for each row in Table B, I would write my query like this: select max(grade) from TableA group by tableBID However, I don't jus...

Want to calculate the sum of the count rendered by group by option..

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 no of transactions entered in the table, a single transaction may be inserted in more than one row, for example, id tid companyid ttype...

SQL query giving "not a GROUP BY expression" error

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 BY RQST_DT) a, (SELECT TO_CHAR(RQST_DT,'YYYY-MM-DD') AS DATEEE, WEEK_NUM FROM TIMEDIM) c, (SELECT NoOfUsers, accDate FROM...

Calculating average (AVG) and grouping by week on large data set takes too long

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 week from pricehistory where timelog > $range and product_id = $id GROUP BY week The only week that actually gets data changed and is worth ...

Is there a name for this type of algorithm?

I have a 2 dimensional array forming a table: [color][number][shape ] ------------------------- [black][10 ][square ] [black][10 ][circle ] [red ][05 ][triangle] [red ][04 ][triangle] [green][11 ][oval ] and what I want to do is group largest common denominators, such that we get: 3 groups group #1: color=bl...

sql group by with left join

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 departmentCode , max(org_surveylog.actiondate) from org_surveylog left join org_division on (org_surveylog.division_code= org_division.division_code a...

Strange problem with ActiveRecord summing grouped values

I'm working on a timesheet system (thrilling, I know) and am having difficulty using ActiveRecord to total the time spent working on particular projects on each day. I have a TimeBooking model: t.references :assignment, :null => false # (Combination of employee and project) t.date :date, :null => false t.integer :durat...

MYSQL - Group by limit

Hello 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. select distinct(rating_name), id_markets, sum(rating_good) 'good', sum(rating_neutral)'neutral', sum(rating_bad) 'bad'...

I don't find the sql request

Hi everybody, 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 measures with a sql request -> 2 first lines in my case because the pairs(src and dst) aren't the same. I try to use DISTIN...

Mysql - Grouping the result based on a mathematical operation and SUM() function

Hi all... 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 10 1 3025 Table : reservation reservation_id start_date end_date room_type booked_rooms ...

TSQL: finding unique entries in a single table

Consider a table or CTE structured like this: Name Num ---- ---- Abc 12 Abc 12 XYZ 70 XYZ 80 XYZ 85 Bar 50 Bar 55 Foo 44 Foo 44 Baz 88 The requirement is to determine the Name where multiple different Nums exist. The desired resultset is Name ---- XYZ Bar What TSQL stateme...

SQL statement HAVING MAX(some+thing)=some+thing

I'm having trouble with Microsoft Access 2003, it's complaining about this statement: select cardnr from change where year(date)<2009 group by cardnr having max(time+date) = (time+date) and cardto='VIP' What I want to do is, for every distinct cardnr in the table change, to find the row with the latest (time+date) that is before year...

Get the count of items in a GroupBy Linq-to-sql Query?

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 == 4).GroupBy(q => q.AnswerNumeric).Where( g => g.Key == 1) In Linq Pad I can see there are 4 items in this query. If I do Count it returns 1....

I can't make this query work with SUM function

This query gives an error: select ep, case when ob is null and b2b_ob is null then 'a' when ob is not null or b2b_ob is not null then 'b' else null end as type, sum(b2b_d + b2b_t - b2b_i) as sales from table where ... group by ep, type Error: ORA-00904: "TYPE": invalid identifier When I run it with group by ep, the...

Why doesn't Oracle SQL allow us to use column aliases in GROUP BY clauses?

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 BY expressions makes the query longer and less readable. Why doesn't Oracle SQL allow us to use the column aliases in GROUP BY clause? There...

Mysql query different group by

Hello i have a products table that contains normal products and configurable product It has a basic stucture of: id name price configurable ('yes', 'no') id_configuration Normal products have configurable no and 0 as id configuration, and configurable products have it set to yes and have the same id_configuration value. The curren...