SQL Error - GROUP BY

Help, (Select...Group By..) Result Error
I have data like this :
ord_empno | ord_date | ord_code
00136 | 1996-07-08 | 00010
00136 | 2003-12-31 | 00020
00136 | 2004-01-01 | 00070

I'd like to select data with the latest date (row 3).

sql : SELECT ORD_EMPNO, MAX(ORD_DATE) ORD_DATE, ORD_CODE
FROM TB_PSN_ORDER
WHERE ORD_EMPNO = ?' GROUP BY ORD_EMPNO

but the result
00136 | 2004-01-01 | 00010

What's wrong with that ? It seem it takes only the first ord_code.

---

Error:invalid Use Of Group Function
version:mysql 4.0.18 for win

hi,all dear:)

when execute sql-statement,
error raise:

update customer,cu_order set customer_point=0
where customer_keyid=order_customerid and
DATE_SUB(now(),INTERVAL 1 YEAR)=DATE_FORMAT(max(order_time),"%Y-%m-%d 23:59:59")

error:invalid use of group function

i beg someone tells me why?how resolve?

thx

---

Invalid Use Of Group Function (was "Baffled By Query Error")
trying to figure out why I keep getting this error with the following query:

SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins
FROM account a LEFT JOIN calls c ON c.account_id = a.id
WHERE c.calldate >= DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 2
AND SUM(c.agent_product_time) >= '500' GROUP BY c.account_id ORDER BY mins

ERROR: #1111 - Invalid use of group function

---

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?

---

"group By" Error
Im trying to output the country name where the population is less than the sum of all the cities populations within that country. Also, to output the difference between the two population values.
Here is what i have:

select c1.name, sum(c2.population)-c1.population
from cities2 c2, country c1
where c1.c_id=c2.country
group by c1.name
having sum(c2.population)>c1.population;

But i keep getting errors about the group by expression.

---

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 >= &#55614;&#57157;-01-01' AND sched2.date < &#55614;&#57158;-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 >= &#55614;&#57157;-01-01' AND sched.date < &#55614;&#57158;-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 |
+---------------------+-----------------------+------+-----+---------+----------------+

---

GROUP BY Only Displaying 1 Row Within Each Group
I have a database that consists of various sports schedules.

The part that is NOT working is GROUP BY. I am trying to group by a column titled "sport"

My current query:

SELECT * FROM `otis_sports` WHERE season = 'Fall' GROUP BY sport

---

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.

---

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?

---

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

---

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

---

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.

---

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=&#391;'
AND attachment.postid>0
AND d.sizeid IN(1,2,6,18)
AND d.retailprice >0
GROUP BY d.sizeid");.......................................

---

Group By
I have a table t_photos from which i want to make a top 20 of all photos so i try:

select * from t_photos where user_sex = '0' and status='1' and rated_times > '15' and rated_times < '900' group by user_id order by score desc

i put 'group by user_id' because i want one user to have only one photo in the top 20 list of course photo with highest score, but this query does not return record of the photo with highest score. For example if user has 5 photos ( there is 5 records in table t_photos with user_id = '11') and one of them has score 5.2 another has 4.86 and rest of the photos has some score, the query above returns the record that has 4.86 in score field.

Now if i remove group by from the query it does sort records by score so photo with score 5.2 appears above all, but there will be 2 records of same user in the top 20.

Is there any solution for this?

---

GROUP BY
I have two tables:

- cars
- manufacturers

Each with many, many records. I want to display them in a drop-down menu like this:

manufacturer1
- car1
- car2
- car3
..so on
manufacturer2
- car1
- car2
- car3
..so on
and so on

I've gotten as far as:

SELECT id, name, manufacturer FROM cars GROUP BY manufacturer, name;

But I have no idea what to do next. Can anyone help me?

---

SUM() &amp; 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.

---

Group By
i am having trouble getting this to work.

what i want is something like this

my table has 2 fields

state and city

i would like to group all the citys by state so i have a list of citys under the state

like:

Minnesota
-------------
Minneapolis
St Paul

California
-------------
Los angeles
San Diego
San Francisco

but when i do a group by i only get one city for each state

SELECT * FROM state group by state order by state

---

GROUP BY
I'm an SQL newbie attempting to develop a query.

Suppose I have the following table
CREATE TABLE t1 (
  a varchar(50) default NULL,
  b varchar(50) default NULL
)
and values
   a    b
---- ----
   x    1
   y    2
   y    3
   z    4

I want to list all occurences of "b" for each "a" with more than one of the same values.  In this case I want to list
   a    b
---- ----
   y    2
   y    3

I have tried:
SELECT a, b FROM t1 GROUP BY a HAVING count(*) > 1;
which gives
   a    b
---- ----
   y    3

Any suggestions?

---

Group By
How does this work? I've tried it but I don't know what it's doing.

---

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.

---

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.

---

Group By
If I have the following data in a table (questions)...


qID language question
--------------------------------
1 GB 'A question in english...'
1 SE 'En fråga på svenska...'
2 GB 'Another question in english...'


... and I want to get all questions, preferbly in english, but if the question doesnät exist in english I want it in (any) other language...

I've tried SELECT * FROM question GROUP BY qID ORDER BY qID but it seems I have no control over which language of the group I'll get... Any ideas?

---

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

---

GROUP BY
I checked the mysql manual and don't see the problem with my syntax:

SELECT id, headline, permalink, body FROM anewsrelatedlinks_1_08 GROUP BY headline WHERE headline LIKE '%Kenyans%' ORDER BY dateUnix DESC LIMIT 5

gives the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE headline LIKE '%Kenyans%'

while

SELECT id, headline, permalink, body FROM anewsrelatedlinks_1_08 WHERE headline LIKE '%Kenyans%' ORDER BY dateUnix DESC LIMIT 5 .

---

GROUP BY
I have a table of customer problems, and another table of cummunications relating to the problem.

I'm trying to pull out for each problem: details of the problem, the most recent comversation, and the timestamp of the oldest conversation.

I've got the latest conversation like so:
CODEselect * from
tProblems p
INNER JOIN
(
SELECT * FROM tProblemComments order BY uts desc
) as pc ON p.poNumber = pc.poNumber
WHERE open = 1
group by p.poNumber
order by p.poNumber asc

---

Group By All
I've have a query with about 15 columns in it, one of them being a count() function.

Obviously as I'm using an aggregate function I have to use the  GROUP BY statement.

My question is, is there a 'shortcut' way of saying group by *all* columns except the one that I'm aggregating, to save having to list the 14 columns explicitly in the GROUP BY statement?

i.e. something like GROUP BY *   ...which I know doesn't work, cos I've tried it.

---

Group By
i add the ip numbers and page numbers that visitors visited to a table. i want show them by ip and the visited pages all. but i want group visitors but i want see all visited pages too. but when i tried to group by ip only one visited page numbers had shown. i used it on php.

I am a beginer that my study subject is economic scinces (phd) but i like work with php and mysql.

---

GROUP BY
I can use subqueries to select from the following table to produce the
output:

Total200420052006
----------------------------
10244



foobar
---------------
DATE added_date



I was told that I can use GROUP BY (I think) instead.

I only managed to do the total which is

SELECT COUNT(*)
FROM foobar;

---

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.

---

SUM With GROUP BY
how can i do it right way ?
My broken query:
CODESELECT
a.ID
,SUM(IF(h.CenaPromocyjna,h.CenaPromocyjna,SUBSTRING_INDEX(MAX(CONCAT(g.DataZmiany,'|',g.CenaSprzedazy)), '|', -1)) * f.Ilosc)
 FROM sklep_Zamowienia AS a
 LEFT JOIN sklep_ZamowieniaProdukty AS f ON f.IDZamowienia=a.ID
 LEFT JOIN sklep_ProduktyCeny AS g ON g.IDProduktu=f.IDProduktu
 LEFT JOIN sklep_ProduktyPromocje AS h ON (h.IDProduktu=f.IDProduktu AND h.OdKiedy <= a.DataZamowienia AND h.DoKiedy >= a.DataZamowienia)
 GROUP BY a.ID

---

Group By
SELECT date, qty FROM epic.orders where qty>0 group by date;

this table has 2 million rows with dates for the entire year, but the query only finds dates up to February.

---

Using GROUP BY
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!

---

Group By
I have a table with names and addresses.
I want a query which will give me all of the distinct address. I also want a field which concats all of the names at that address. So if two people live at the same address, I want just one row with the adress and I want the two names in the last field.

What I've been able to do is this:
select count(First_Name), House_Number, Street_Direction, Street_Name, Street_Type, First_Name, Last_Name, into outfile '/tmp/foo.csv' fields terminated by ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '
' from list where Zone_Code = "105" group by (concat(House_Number, Street_Direction, Street_Name, Street_Type)) order by Street_Name;

That gives me only one line for addresses, but it also only gives me one name. Can anyone help?

---

Group By
Select filename, bucket, count(term) from table group by Bucket where filename like '%filename5-01-2006%'
So I have about 20 filenames. Rather then running the SQL above and having to manually enter each filename I would like to return the data just like above but for each filename also. So for example it would return..

filename5-01-2006 | 12 | 432453
filename5-01-2006 | 13 | 345453
filename5-01-2006 | 14 | 254363
filename4-01-2006 | 12 | 888853
filename4-01-2006 | 13 | 997553
filename4-01-2006 | 14 | 154363
...

---

GROUP BY
I have a raffle system i am making that selects 5 random winners from a table. I would to be sure that the same user doesnt get selected twice. Is there a specific mysql function for that? I was thinking something like this:

SELECT * FROM raffle WHERE id='2' GROUP BY uid ORDER BY RAND() LIMIT 5

---

GROUP BY
select max(created_date), loan_id, change_id, comment_id from loan_change_history group by loan_id

Here I want loan_id and change_id to come corresponding to the max(created_date) which is grouped by loan_id but it doesn't fetch the correct values

---

Group By
I am trying to create a query that groups by weeks...is this even possible? While doing this I am trying to count all items in this week. Example:
date | units | cost per unit
Jan 1 | 78 | 0.40
Jan 3 | 35 | 0.44
Jan 5 | 44 | 0.79
Jan 5 | 55 | 0.40
Jan 6 | 48 | 0.40
Jan 8 | 66 | 0.40
Jan 9 | 20 | 0.40
What I am trying to see
Jan 1 - 7
Total Cost = $X.XX
The total cost would be the (units * cost per unit) then all rows that fall in the date range.
I have no clue how to even start to do this.

---

GROUP BY
Is this possible?

I've got a Union of 2 SELECT statements that I need to GROUP based on 2 different columns.

Can you use GROUP BY somehow with UNION? (Not within the SELECT statements but after/during the UNION)

---

GROUP BY
I need to GROUP a query in MySQL by the combination of 2 columns . (basically company # and branch #) Basically the table (not one i created) is a list of branches. All branches have their company number and a branch number that is unique only to that company.This is a legacy table from the mid 90s and just want to make sure I'm grouping them right.

Would it simply be:

GROUP BY company, branch ?

---

GROUP BY
I query my database and want the output to be grouped. No big deal as I just use 'GROUP BY'. But how can I choose which row gets selected to show up? Let's say I have 3 rows and I want the 1 row to show up which has the highest value in one of the columns, how can I do this? I hoped it would be something like 'GROUP BY `channel` MAX(erp_kw)' but it did not work.

---

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.

---

GROUP BY
My query
SELECT loan_name FROM loan GROUP BY loan_id
The result is

loan_name date
abc 23

but the last update was on 27 so I want it to display 27

---

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?

---

GROUP BY
I want to take last 10 voted members by excluding same members.
Table is like this:

id / member / vote
1 / 10 / 1
2 / 15 / 0
3 / 32 / 0
4 / 10 / 0

I think this query is ok but not working:
SELECT member, COUNT(*) FROM table GROUP BY member ORDER BY id DESC LIMIT 10

---

Using GROUP BY
I have a table with many fields and among them I have an index called `id` and a timestamp called `timestamp`.I want to be able to select rows according to my WHERE clause, but only access the rows with the greatest `timestamp` for each `id`. As if the table is really only composed of one instance of each value of `id`.
So I am using the GROUP BY on `id` to limit the results to one row per unique id.Code:

SELECT * FROM `person` WHERE `company_id` = 1 GROUP BY `id` ORDER BY `timestamp` DESC, `last_name`, `first_name`

Which gives me one row per value of `id`, but I can't figure out how to get it to order by the latest timestamp in each grouping. The ORDER BY clause in the example doesn't work.I have tried approaches using DISTINCT and HAVING, but I can't seem to get it right.

---

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?

---

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)

---

Group By
I want to select only each account have more than 8 entries (totalcount).
I need to put "totalcount > 8" somewhere but i'm getting error.Code:

select account, count(*) as totalcount from newips
where account not in ('none', 'test')
group by account order by totalip desc

---

GROUP BY
I would like someone to clarify to me why in grouping queries, all of the columns in the select statement should appear in the group by clause.
I have tried some queries where I either put all of the selected columns in the group by or only one and the difference I noticed is the order in which they are displayed.

---