Group Of Groups
Basically, I have a simple group query...
SELECT `StudentID`, COUNT(*) FROM `StudentHistoryT` GROUP BY `StudentID`
The query returns the number of records for each student.
What I want is a query that produces a count of the number of students with a distinct number of records, that is, I want to group by COUNT(*) and return the COUNT(*) of the new group.
My nieve atmysqlt...
SELECT `StudentID`, COUNT(*) FROM `StudentHistoryT` GROUP BY `StudentID` GROUP BY COUNT(*)
View Complete Forum Thread with Replies
Related Forum Messages:
Using A Single Sql Statement To Group And Count The Groups
I'm trying to count the total number of items in a table that are grouped e.g. my table is a shopping basket and is like this: basketID||orderID||productID||quantity 1||1||2||1 2||1||3||1 3||1||4||1 4||2||2||1 5||2||4||1 6||3||4||1 so product 4 appears 3 times, product 2 appears twice and product 3 appears just once how do use an sql statement (if it is possible) to group the products then list them in the order of which appears most so i could say SELECT * FROM basket GROUP BY productID; and that would group them for me, but i want them listed like productID 4 2 3 (as 4 has the most occurances, then 2, then 3)
View Replies !
Order Per "Group By" Groups
a table example: Code: ID | USER | VALUE | 1 | mike | 8 2 | mike | 10 3 |mike | 12 4 | john | 10 5 | john | 12 6 | john | 15 Desired result grouped by USER: ID | USER | VALUE | 6 | john | 15 3 | mike | 12 as you can see, I'm ordering the group result by the last value of ID column for each user, then order by user name. In other words, I need to retrieve the max ID value for each user. also order the result by user name. How can I do that ? Myabe there's no need of grouping,
View Replies !
Groups
I have: col1col2 item13 item15 item27 item23 item14 item34 I'm looking for the result: col1col2 item15 item27 item34 --the highest col2 value for data paired with col1 --no duplicates in col1 --order does not matter for either col1 or col2 in the result The SQL I have now is: select col1, col2 from table1 group by col1 I'm not sure how to force it to group col1 using the highest value from col2. Also, I am on MySQL 4.0 so subqueries are out. I'm not sure if this is relevant, but table1 is made by joining two other tables together (one of which is a heap).
View Replies !
Groups For Members
My client wants GROUPS for his members, what I am thinking is creating a table for each group - since each group has a leader ID number and member list. Would this be a good way to do it?.
View Replies !
Order By Giving Two Groups
I have a new database table which contains name and url field. When I order by name I get two groups, the first being those without an entry in the url field, the second with an entry in the url field, each grou seperately ordered correctly. In a possibly related problem with the same table displaying in a web browser through php, there are again two groups diplaying when ordered by name. If a member of the first group is displayed and resubmitted without change, it then appears in the second group. I tried doing an export / import to refresh the data, but it didn't help. Any ideas for causes and or solutions?
View Replies !
Two Servers On Windows And My.cnf/my.ini Groups
I run two MySQL servers on Windows with the two services. I read in the docs that it exists a way to identify the servers in my.cnf by groups and about a tool from Linux to manage the groups. How can I start a server on Windows through the my.cnf/my.ini 's groups?
View Replies !
Return Groups For Each Month
I would like to return monthly reports with a single query.For example: January 15 Purchases 5 Refunds 6 Exchanges February 20 Purchases 4 Refunds 2 Exchanges The above data has about 60 records. The query has to group by month and then again by transaction type. Here is what the record would look like TransactionID - primary key TransactionDate - Date Transactiontype - integer
View Replies !
Counting And Ranking Within Groups
(commas are in place to show data seperation and are not needed in the result set) Name Won Julio, 2 julio, 3 julio, 0 Ron, 4 Ron, 2 and the results should be based upon wins sorted descending Name Won Rank Julio, 3, 1 julio, 2, 2 julio, 0, 3 Ron, 4, 1 Ron, 2, 2
View Replies !
Select First X -groups- Of Rows
What I've got is 3 tables: news, category, newscategory. newscategory is just a linker table, so the relationship is like this: [news.newsid] <--> [newscategory.newsid][newscategory.categoryid] <--> [category.categoryid] One entry in 'news' can be attached to many different categories via 'newscategory'. What I want to do is return the first X news posts from 'news', along with all the categories that each post belongs to. I can't figure out how to do this: using an INNER JOIN there will be multiple rows for each news post as there will be multiple categories associated with each post, (one row per category per post) but the LIMIT 0, X clause will apply to all the rows,
View Replies !
Full Yearly Weekly Group By Group Report
I have table called tblTransactions: TblTransactions Group | Months | Year | Transaction A | 3 | 2007 | 45 B | 2 | 2007 | 89 A | 7 | 2007 | 50 A | 8 | 2007 | 34 B | 4 | 2007 | 55 A |12 | 2007 | 10 A | 1 | 2008 | 88 B | 3 | 2008 | 34 B | 5 | 2008 | 70 A | 5 | 2008 | 45 A | 9 | 2008 | 88 B | 7 | 2008 | 99 A |10 | 2008 | 77 A |11 | 2008 | 99 How is the T-SQL to make the result like this (the result will display all of the week (FULL), the target is I can compare week by week between years and group. A | 1 | 2007 | 0 B | 1 | 2007 | 0 A | 2 | 2007 | 0 B | 2 | 2007 | 89 A | 3 | 2007 | 45 B | 3 | 2007 | 0 A | 4 | 2007 | 0 B | 4 | 2007 | 55 A | 5 | 2007 | 0 B | 5 | 2007 | 0 A | 6 | 2007 | 0 B | 6 | 2007 | 0 A | 7 | 2007 | 50 B | 7 | 2007 | 0 A | 8 | 2007 | 34 B | 8 | 2007 | 0 A | 9 | 2007 | 0 B | 9 | 2007 | 0 A | 10| 2007 | 0 B | 10| 2007 | 0 A | 11| 2007 | 0 B | 11| 2007 | 0 A | 12| 2007 | 10 B | 12| 2007 | 0 A | 1 | 2008 | 88 B | 1 | 2008 | 0 A | 2 | 2008 | 0 B | 2 | 2008 | 0 A | 3 | 2008 | 0 B | 3 | 2008 | 34 A | 4 | 2008 | 0 B | 4 | 2008 | 0 A | 5 | 2008 | 45 B | 5 | 2008 | 70 A | 6 | 2008 | 0 B | 6 | 2008 | 0 A | 7 | 2008 | 0 B | 7 | 2008 | 99 A | 8 | 2008 | 0 B | 8 | 2008 | 0 A | 9 | 2008 | 88 B | 9 | 2008 | 0 A |10 | 2008 | 77 B |10 | 2008 | 0 A |11 | 2008 | 99 B |11 | 2008 | 0 A |12 | 2008 | 0 B |12 | 2008 | 0
View Replies !
Group By With Order BY, Or INSERT INTO (SELECT * GROUP BY)?
I seem to have a few duplicates entries in a very large database. My database has two identical tables. One is written to on a regular basis (every few seconds) and the other is used for queries, and updated from the write table on a daily basis. I'm trying to get rid of duplicate entries for the queries, and I thought the most effective way to do this would be to run the query as Expand|Select|Wrap|Line Numbers
View Replies !
GROUP BY With Order Inside Group
I have a problem when grouping records - I can't manipulate data inside group. For example, I have table `images` with fields `name` (name of image, not unique) and `dtadded` (date of image adding). Then, I need to get all images names with distinct names where each name must be latest added name. Sample: ------------------------------ id, name, dtadded ------------------------------ 1 name1 2007-10-15 00:00:00 2 name2 2007-10-15 00:00:00 3 name1 2007-10-16 00:00:00 ------------------------------ I need to receive 2 results (for each name) 3 - name1 - 2007-10-16 00:00:00 2 - name2 - 2007-10-15 00:00:00 If I use SQL code: SELECT images.id, images.dtadded, images.name, count(name) FROM images GROUP BY `name` ORDER BY dtadded DESC I get results 1 - name1 - 2007-10-15 00:00:00 2 - name2 - 2007-10-15 00:00:00 It groups records with first row in database, but I need last row in table for each name. Question: How can I order results in side group to get needed results as described above?
View Replies !
Top Record From Each Group In GROUP BY
I never could figure out a good way to do this. I am doing a group by statement, and want the top record from each group to be returned only. My query is to find the current win/loss streak for each team. Here is the sql: SELECT id, team_name, result, MIN(date) AS startdate, MAX(date) AS enddate, COUNT(*) AS games FROM ( SELECT t.id, t.team_name, sched.date, CASE sched.draw WHEN 1 THEN 'draw' ELSE CASE sched.winner_id WHEN t.id THEN 'win' ELSE 'loss' END END AS result, ( SELECT COUNT(*) FROM fantasy_schedule sched2 WHERE ( CASE sched2.draw WHEN 1 THEN 'draw' ELSE CASE sched2.winner_id WHEN t.id THEN 'win' ELSE 'loss' END END ) <> result AND sched2.date >= ��-01-01' AND sched2.date < ��-01-01' AND sched2.date < sched.date AND sched2.fantasy_league_id = 6 AND (sched2.fantasy_team1_id = t.id OR sched2.fantasy_team2_id = t.id) ) AS rungroup FROM fantasy_schedule sched JOIN fantasy_team t ON t.id = sched.fantasy_team1_id OR t.id = sched.fantasy_team2_id WHERE sched.last_update < NOW() AND sched.date >= ��-01-01' AND sched.date < ��-01-01' AND sched.fantasy_league_id = 6 ) bighonker GROUP BY id, team_name, result, rungroup ORDER BY enddate DESC And a subset of what is returned: +----+----------------------------+--------+------------+------------+-------+ | id | team_name | result | startdate | enddate | games | +----+----------------------------+--------+------------+------------+-------+ | 1 | The Macho King | loss | 2005-12-17 | 2005-12-17 | 1 | | 1 | The Macho King | win | 2005-12-11 | 2005-12-11 | 1 | | 1 | The Macho King | loss | 2005-11-24 | 2005-12-04 | 2 | | 1 | The Macho King | win | 2005-11-20 | 2005-11-20 | 1 | | 1 | The Macho King | loss | 2005-11-13 | 2005-11-13 | 1 | | 1 | The Macho King | win | 2005-10-30 | 2005-11-06 | 2 | | 1 | The Macho King | loss | 2005-10-16 | 2005-10-23 | 2 | | 1 | The Macho King | win | 2005-10-09 | 2005-10-09 | 1 | | 1 | The Macho King | loss | 2005-10-02 | 2005-10-02 | 1 | | 1 | The Macho King | win | 2005-09-08 | 2005-09-25 | 3 | | 2 | General Grievous | loss | 2005-12-17 | 2005-12-17 | 1 | | 2 | General Grievous | win | 2005-12-11 | 2005-12-11 | 1 | | 2 | General Grievous | loss | 2005-11-24 | 2005-12-04 | 2 | | 2 | General Grievous | win | 2005-11-13 | 2005-11-20 | 2 | | 2 | General Grievous | loss | 2005-11-06 | 2005-11-06 | 1 | | 2 | General Grievous | win | 2005-10-23 | 2005-10-30 | 2 | | 2 | General Grievous | loss | 2005-10-16 | 2005-10-16 | 1 | | 2 | General Grievous | win | 2005-10-02 | 2005-10-09 | 2 | | 2 | General Grievous | loss | 2005-09-08 | 2005-09-25 | 3 | | 3 | Ultimate Jarin | win | 2005-11-06 | 2005-12-24 | 8 | | 3 | Ultimate Jarin | loss | 2005-10-23 | 2005-10-30 | 2 | | 3 | Ultimate Jarin | win | 2005-10-09 | 2005-10-16 | 2 | | 3 | Ultimate Jarin | loss | 2005-09-25 | 2005-10-02 | 2 | | 3 | Ultimate Jarin | win | 2005-09-08 | 2005-09-18 | 2 | | 4 | The Hulkamaniacs | loss | 2005-12-11 | 2005-12-11 | 1 | | 4 | The Hulkamaniacs | win | 2005-12-04 | 2005-12-04 | 1 | | 4 | The Hulkamaniacs | loss | 2005-11-24 | 2005-11-24 | 1 | | 4 | The Hulkamaniacs | win | 2005-11-06 | 2005-11-20 | 3 | | 4 | The Hulkamaniacs | loss | 2005-10-30 | 2005-10-30 | 1 | | 4 | The Hulkamaniacs | win | 2005-10-09 | 2005-10-23 | 3 | | 4 | The Hulkamaniacs | loss | 2005-09-18 | 2005-10-02 | 3 | | 4 | The Hulkamaniacs | win | 2005-09-08 | 2005-09-08 | 1 | | 5 | WHO WANTS TO KISS HOMER? | win | 2005-12-04 | 2005-12-04 | 1 | | 5 | WHO WANTS TO KISS HOMER? | loss | 2005-11-20 | 2005-11-24 | 2 | | 5 | WHO WANTS TO KISS HOMER? | win | 2005-11-13 | 2005-11-13 | 1 | The top record in each group is the current streak. I want only that top record for each teamr returned. I could simply 'order by enddate DESC' and in my php script loop until I have one record per team, but is there any way I can only return the top result for each team in the query itself? Here is the corresponding table schema: +-------------------+---------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------------------+----------------+ | id | int(10) unsigned | | PRI | NULL | auto_increment | | fantasy_league_id | int(10) unsigned | | MUL | 0 | | | week_num | tinyint(3) unsigned | | MUL | 0 | | | fantasy_team1_id | int(10) unsigned | | MUL | 0 | | | fantasy_team2_id | int(10) unsigned | | MUL | 0 | | | winner_id | int(10) unsigned | YES | | 0 | | | date | date | | | 0000-00-00 | | | last_update | datetime | | | 0000-00-00 00:00:00 | | | draw | tinyint(3) unsigned | | | 0 | | +-------------------+---------------------+------+-----+---------------------+----------------+ fantasy_team +---------------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+-----------------------+------+-----+---------+----------------+ | id | int(10) unsigned | | PRI | NULL | auto_increment | | fantasy_league_id | int(10) unsigned | | MUL | 0 | | | user_id | mediumint(8) unsigned | | MUL | 0 | | | team_name | char(40) | | | | | | fantasy_division_id | int(10) unsigned | YES | MUL | NULL | +---------------------+-----------------------+------+-----+---------+----------------+
View Replies !
SQL & GROUP BY
I have a rather simple SQL statement, but I can't get it to work properly. I have four columns in the activities table "LoginID", "StartDateTime", "StopDateTime", and "ProjectID". In a second table, Projects, I have "ProjectID" and "ProjectName". The goal of the SQL statement is to join the two ProjectID together, and get the difference in the stop and start time and any projects that are the same, sum or group them together. My SQL statement looks like this: Expand|Select|Wrap|Line Numbers
View Replies !
GROUP BY/HAVING
I have 3 columns: Code: --COL1---COL2----ID3---- --R123---Meg----1001--- --R123---Dan----1006--- --A343---Meg----1009--- --R123---Meg----1011--- --K564---Mike---1101--- --G453---Dan----1016--- --B757---Pete---1219--- --T468---Meg----1453--- (ID3 is the primary key) I want to find rows that have more than 1 COL2 value for COL1. Above there is only R123 (1001, 1006) that is like that. I haven't used GROUP BY much. Maybe never...If someone could provide an example it would be very useful. I've tried a few things based on other SQL I've seen, but I just couldn't figure out how to use 2 coulmns (COL1, COL2)
View Replies !
Group By Name
I have some product which have almost the same name: AMD Athlon 64 3000+ Box 1.8 GHz AMD Athlon64 3000+ Box SOCKE AMD Athlon64 3000+ BOX S.939 I want this to be grouped in : AMD Athlon 64 3000+ Can I make this?
View Replies !
Max In Group
I will try and describe my question as brief as possible. I need an sql statement that can: select the latest (limit) entries grouped by 1 column.
View Replies !
Using GROUP BY
Mysql.org was no help on this. I have a select which will return me a list of dates and the number of times they occure in the table. I would like to group the dates (ie 2001-09-20) not on the day but on the month so all dates from 2001-09 will get grouped and counted togther. Am I right to think this should be done by GROUP BY or maybe HAVING should be used to help.
View Replies !
Using GROUP BY And Having
Say I have a result that returns 200 rows, but before returning the rows I include GROUP BY planes HAVING speed < 200. speed is calulated in the SELECT part of the query using 3 columns and some data from another query that is used to determine the speed. So when I use mysql_num/affected_rows() it returns 200 rows, but the real return is different, in this case 189 rows are returned because of using HAVING. So how does one get the real number of rows returned, which is 189!
View Replies !
SUM() & GROUP BY
i have one table called totals wich has many entries with sales and payments. It has another information like date, and client name, i even added a id auto_increment column to try to fix my problem. Te problem is the following: I want to add and sustract sales and payments to get the current total debt. I have tryed with the 'SUM(sales-pays) GROUP BY date' but it only works with entries from the same date and it doesn't show the payment and the sale separately. I guess that the correct form of getting this total debt is using 'SUM(sales-pays) GROUP BY client name' but if i do it that way i won't get the state of the debt day by day. The english is not my first language so i tried to explain it the best i could.
View Replies !
Something Like Group By?
i have this table: char, num, descript A, 1, descript#1 A, 2, descript#2 B, 1, descript#3 A, 3, descript#4 B, 2, descript#5 i whould like to get one rows per char with the max num, with the table before: A, 3, descript#4 B, 2, descript#5
View Replies !
Sum Of A Group
I am working on a sales tracking project. See below query SELECT products.product, products.price, count( log.product ) AS 'Count', ROUND(price*count(log.product), 2) AS 'Total' FROM products LEFT JOIN log ON products.product= log.product WHERE log.date_sold between 'colname' and 'colname2' and log.employee_id = 'colname3' GROUP BY product +------------------------------+-------+-------+-------+ | product | price | Count | Total | +------------------------------+-------+-------+-------+ | ATM Card | 3.00 | 2 | 6.00 | | Audio Response | 3.00 | 0 | 0.00 | | Check Card | 5.00 | 1 | 5.00 | | Courtesy Pay | 5.00 | 2 | 10.00 | This is exactly what I want. However, I would also like to sum the complete total row. So the total of that would be 21.00 I am stuck on how to sum that up. I tried sum(price*count(log.product), but MySQL does not like that.
View Replies !
GROUP ID
I downloaded mySQL version for HP UNIX 11 (PA-RISC 2.0) to my HP UNIX node. In the instruction, it said "groupadd mysql" I got error "Group 'mysql' not unique" I checked my previous /etc/group file, there was no "mysql" entry at all. Does it mean a specific group number has to be assigned to mysql group? If so, what group ID will mysql use? Can I use any other group ID?
View Replies !
Age Group
How can I calculate the number of person in a particular age group, i.e.: age 12-18, age 19-25 and so on? Also, can I know where is the Mysql ROOT? I'm using Mysql 4 and JSP, if that helps.
View Replies !
GROUP BY Or What To Use?
I'm helping my daughter with a website for her dance classes. I have a schedule with a repeated region listed for all the classes. It's listen in alphabetic order for every type of dance, like Breakdance (3 of them), then Streetdance (4 of those), and so on. But when this is echoed it looks a little messy. Wish I could keep the table together for every type of dance and then like on the next couple of rows it will be a new one listing the next kind of style of dance. Just don't remember how to do this and pick the right stuff in right order.Does anyone have a tip or a link to where I can find more info? What I have this far is something like this: PHP SELECT * FROM dance_coarse, dance_teacher WHERE dance_teacher.teacher_id = dance_coarse.teacher GROUP BY dance_coarse.dance ORDER BY dance_coarse.dance ASC But this one will only list one class for every style. I wanna have the rest listed as well.
View Replies !
Group Group By
Is it possible to do a GROUP GROUP BY? ID Description TypeID Something like this... I want to create groups by the TypeID. Something like this... SELECT COUNT(ID) as Counted, Bla FROM table GROUP BY TypeID(1,4,6), TypeID(2,3,5) as Bla
View Replies !
Help With GROUP BY?
I am expanding an old query which worked just fine. The new tables I'm including in the query seem to have some duplicate rows (possibly legitimately, I'm not sure) and it's screwing up my results. However I don't seem to be able to weed the duplicates out of my query. Can anyone help? SELECT from_unixtime(uts,'%a %d-%m-%Y') AS thisday , uts , o.orderid ,o.vatRate , round(sum(op.salePrice * op.prodQty) + o.delivery - o.discount,2) as ordersale , round(sum(op.costPrice*op.prodQty) + o.deliveryCost,2) as ordercost FROM tOrders o INNER JOIN tOrderPOs pos ON o.orderId = pos.orderID INNER JOIN tOrderProds op ON pos.purchaseOrder = op.purchaseOrder INNER JOIN tCustAddrs ca ON o.custID = ca.custID AND type=1 INNER JOIN tAddress a ON ca.addressID = a.addressID WHERE uts > 1157065200 GROUP BY o.orderid , o.deliveryCost ORDER BY uts The duplicates are in tCustAddrs such that a given custID seems to sometimes have more than 1 addressID. So the join is creating multiple rows for an order, and it's counting the sale value twice in the SUM. I think that's what's happenning? How do I get around this?
View Replies !
First (id) And Last (id) Per Group
data in myTable (id) countryGroup country (1) 1 Asia (2) 2 Europe (3) 2 France (4) 1 China (5) 3 North America (6) 2 Spain (7) 1 Korea (8) 2 Italy (9) 3 Canada (10) 2 Germany (11) 4 Africa I have data in myTable like the above. The following code will produce the following result. code select id,country from myTable31 group by countryGroup result (1) Asia (2) Europe (5) North America (11) Africa I like to produce my target result below. target result1 (1) Asia (7) Korea (2) Europe (9) Germany (5) North America (10) Canada (11) Africa (group1) Asia is the first (id=1) and Korea is the last (id=7) in the group China is in the same countryGroup(1), but its id(4) is neither the first id nor last id in the group. (group2) Europe is the first (id=2) and Germany is the last (id=9) in the group France, Spain, and Italy are in the same countryGroup(2), but their id(3,6,8) is neither the first id nor last id in the group. (group3) North America is the first (id=5) and Canada is the last (id=10) in the group (group4)Africa is the first (id=11) and there is no last (id) in the group Target Result1 above is categorized per proup for showing. My actual target result is target result2 in the below. target result2 (1) Asia (2) Europe (5) North America (7) Korea (9) Germany (10) Canada (11) Africa
View Replies !
How To Get Only 1 Of Each W/o GROUP BY
I had a query that worked great: SELECT MAX(d.did) as did, MAX(d.threadid) as threadid, MAX(d.postid) as postid, d.size as size, d.sizeid as sizeid, d.type as type FROM d LEFT JOIN attachment ON d.postid=attachment.postid LEFT JOIN thread ON thread.threadid=d.threadid WHERE d.status='' AND thread.visible=Ƈ' AND attachment.postid>0 AND d.sizeid IN(1,2,6,18) AND d.retailprice >0 GROUP BY d.sizeid");.......................................
View Replies !
Group By?
Just wondering if I have: Seven records saved in my database with the date: 2007-06-28 Eight records saved in my database with the date: 2007-06-29 Nine records saved in my database with the date: 2007-06-30 Is there any way I can group the records without actually knowing the date ie writing Where date = in mysql statement.
View Replies !
Group By And Other Columns
I have an SQL statement such as the following. "select sum(transaction_amount), file_number, party_code, t_date from table group by file_number" The file_number can be same for many rows but party_code will be different for each row. How can I get all possible values of party_code field. Currently I get only the party_code for first record.
View Replies !
UNION ALL GROUP BY
I have: SELECT x, count(x) FROM table1 WHERE ... UNION ALL SELECT x, count(x) FROM table2 WHERE... UNION ALL SELECT x, count(x) FROM table3 WHERE ... UNION ALL .... .... and I need to do GROUP BY with all the sentences.
View Replies !
Order By With Group By
If I have a GROUP BY statement, which groups my multiple fields, e.g.: SELECT ......GROUP BY date, store can I have multiple ORDER BY statements with that? e.g.: SELECT ......GROUP BY date, store ORDER BY date asc, store, desc Is this an acceptable syntax?
View Replies !
Get A Certain Element In A GROUP BY
I am trying to control which element gets picked when I do a group by, but I can't figure out how to do it. First some example data: CREATE TABLE t ( id int not null primary key auto_increment, group_id int not null, level int not null); INSERT INTO t VALUES (1,1,2); INSERT INTO t VALUES (2,1,3); INSERT INTO t VALUES (3,1,1); INSERT INTO t VALUES (4,2,1); INSERT INTO t VALUES (5,2,1); INSERT INTO t VALUES (6,2,1); INSERT INTO t VALUES (7,4,4); INSERT INTO t VALUES (8,1,1); INSERT INTO t VALUES (9,2,2); mysql> SELECT * from t; +----+----------+-------+ | id | group_id | level | +----+----------+-------+ | 1 | 1 | 2 | | 2 | 1 | 3 | | 3 | 1 | 1 | | 4 | 2 | 1 | | 5 | 2 | 1 | | 6 | 2 | 1 | | 7 | 4 | 4 | | 8 | 1 | 1 | | 9 | 2 | 2 | +----+----------+-------+ 9 rows in set (0.00 sec) The real schema is of course much more complex. I want to get one line for each "group_id" and it must be the one with the lowest "level,id". Adding "order by level" just orders the results when they are already grouped. mysql> select *,MIN(level) as min_level from t group by group_id order by level,id; +----+----------+-------+-----------+ | id | group_id | level | min_level | +----+----------+-------+-----------+ | 4 | 2 | 1 | 1 | | 1 | 1 | 2 | 1 | | 7 | 4 | 4 | 4 | +----+----------+-------+-----------+ In this case I wanted to get row 3 (with level 1) for group 1 for example.
View Replies !
Group By To Return 1 Row.
Is it at all possible to group by and have it return all variables from a single row based on a max() criteria of a single variable? For instance in SPSS I can sort by id number descending then in an aggregate have it say that newvar2=first(newvar) or last(newvar).?? Just a thought?? any ideas?
View Replies !
GROUP BY Command
I've got a table with following fields and data: int int double article,userid,price 1 1 10 1 2 15 1 1 20 2 1 10 2 2 20 3 2 100 3 1 200 What I want to know is, which user pays the highes price for every article. And this in one sql-command. The Result have to look like this: int int double article,userid,price 1 1 20 2 2 20 3 1 200 Any ideas?
View Replies !
Sum()/Group By Query Help
I have tried searching for a solution to this, but I can't seem to find a good set of keywords, so I'm hoping some kind soul will answer it for me. I'm not sure it is even possible, but it certainly seems as though it should be....
View Replies !
Sort With GROUP BY, HAVING
I've got the following table: id id_items id_fc nombre cantidad unidad precio moneda 1 1 0 Prod1 9612 unidad 152 dolar 21 1 1 prod3 150 NULL NULL NULL 9 1 0 prod3 500 ton 321 peso 20 1 1 Prod1 100 NULL NULL NULL 19 1 NULL NULL NULL NULL NULL NULL I'd like to obtain registers grouped by 'nombre', with the data from the one (from the rows with same 'nombre') that has id_fc = 0 or NULL. This would be a unique row. Also, I want a column to show the result of the SUM of 'cantidad' of all items with that 'nombre' minus the 'cantidad' of the selected item (the one with id_fc = 0 or NULL) So, I tried the following query: ....
View Replies !
Group By Not Using Index
I have 3 tables tableA has info regarding name and clientid tableB has info regarding the orderids of the clients tableC has info regarding the tradeids of the clients And all the tables have session column primary key for tableA is (session,clientid, name) tableB has primary key(session, orderid) and index(session, clientid) tableC has index(session, orderid) and here is my query Expand|Select|Wrap|Line Numbers
View Replies !
Reverse Group By
e.g. i have 3 rows of data all related to unit number 313025 as below 313025 - Red - Yellow - NoData 313025 - Red - Green - Green 313025 Green - Green - Green When i do a GROUP BY Unit i get the following 313025 - Red - Yellow - NoData or the fist one in the group that was entered, what i want is on a GROUP BY for it to show up the last record
View Replies !
Bidirectional Group
I have a table that has an origin column and destination column. I want to have a query that will do a bi-directional group by. In other words if one row has FLL as the origin and FPO as the destination and another row has FPO as the origin and FLL as the destination; these two rows should be grouped together.
View Replies !
|
TRACKER
