Group With Two Date Checks

RDBMS Checks
I mean: which RDBMS
1) allows to declare that relationship is in fact 1-1, and
2) protests when one tries to violate the 1-1 relationship,
that is inserts into a table two records with the same
foreign key?

---

MYSQL Health Checks
does any one have a list of dba type checks that they use for mysql?

---

Turning Off Foreign Key Checks
Is it possible to temporarily turn off foreign key checks in a db? I know how to do it from the mysql prompt but I have a number of scripts using Java, etc which seem to be breaking because of the table ordering with respect to foreign keys does not seem to be honored (tables with FKs are declared before the tables the keys refer to).

If there was some way to turn off checking for a db, run all the creation and import scripts I have, then turn the checking back on it would save a ton of effort.

---

How To Group By Date
I have following entries in Mysql database.

/*******/
2007-12-06 08:38:35
2007-12-06 09:02:40
2007-12-06 09:03:08
2007-12-06 09:04:41
2007-12-06 09:05:25
2007-12-07 08:04:28
2007-12-07 08:14:14
2007-12-07 09:01:01
2007-12-07 10:01:02
2007-12-07 11:01:01
2007-12-08 00:01:01
2007-12-08 01:03:02
2007-12-08 01:04:02
2007-12-08 01:05:02
2007-12-08 01:06:02
/******/

What I want is to group by dates without considering time. From above example, I want to get one record for ��-12-06', one record for ��-12-07', and one record for ��-12-08'.

---

Date Functions In Group By
I've got a table with visitor statistics where one of the fields is a timestamp generated with UNIX_TIMESTAMP(). Each hit on the website is recorded as a row.

Now i'd like to get the busiest day, the day with the most hits. Is this anyway possible with the Date and Time functions from Mysql? GROUP BY DAYOFYEAR(ts) or something similar?

---

Using Group By Date And Count
Is there a way to select count(*) by grouping by date, and having multiple
date ranges?

combining...

select field,count(*) from table where datefield > 2004/1/1 and datefield <
2004/1/31
and
select field,count(*) from table where datefield > 2004/2/1 and datefield <
2004/2/29
so that the output is

field 34
field 40

---

Sorting By Date Before Group
I am trying to sort my records by date, with the most current date first, then group it by a criteria. Problem I am having is I can group by [column] but it will only show the first records of each group which are the earlier dated records.

SELECT areas.id AS arid, area, articles.id AS aid, articles.article_title, articles.postdate, articles.areaid FROM articles LEFT JOIN areas ON articles.areaid=areas.id WHERE postdate <= "2004-10-10" GROUP BY arid;

---

Testing The Database? Backing Up, General Checks
I want to know what else I can be doing to check the stability of our database. Currently what I do every evening is download the complete database through PHPMyAdmin on our dedicated server, once downloaded I start up the Wamp Server and import the database locally through PHPMyAdmin on my own machine.

The next thing I do is run the Check Tables command on all the tables to see if everything is where it is. It always comes back as OK on each table so I presume everything is ok?

Is there anything else I should be doing, or is what I am doing correct?

Every so often we delete auctions to free up space in the database. Once this is done we have overhead in the tables. Sometimes this can be 2-10mb, so what I do then is run the optimise command on the tables, again am I correct in doing this?

Finally our database is now over 40mb, to me this massive as the largest database I have worked on before has been 4mb, please dont laugh.

What sort of database sizes have you seen in MySql or are you running at present? What is MySql like in the long run stability wise. If looked after can we get many years of good service from it.

---

Group By Specific Part Of Date
I have a field in a table with the date format. I was wondering if there is a way to group by a specific part of the date instead of the whole date? Meaning I would like to grab all records and group by year in the date column. How can I do that?

---

First Group By Date, Then Count... All In One Query?
I have a mySQL database that has individual records for each time a page on my site is hit. What I'm trying to do is set up a tracking system which will display the hit counts for each page, by month, for the past three months, and ordered by total hits over that timespan.

The table is simple:
visitedPage | visitedTime

My current query is this:

---

Displaying The Latest Date On A Group By Query Instead Of The 1st
I have a select statement that retrieves customer activity. What I want the result to display is each active company in a specified timeframe.

Currently when I do a group by on the result (so that it only shows each company once) the date value has the 1st login instead of the most recent...

Here is the statement so far.....

---

GROUP BY Date With Variable Is Unix Time
I am saving the Unix time every time a record is created in a field called "Time" and would now like to write a report to group on Date ... is there a in-built function to do this ..

SELECT * FROM tblTemp GROUP BY [Date] ?

---

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?

---

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

---

Loading Date Format 1-1-2007 To Date Field Doesn't Work
I'm trying to load the date into a date field in mysql so that I can sort by date, but the date field in mysql is yyyy dd mm or yyyy mm dd so it doesn't load. What can I do?

---

Passing Date Via Form For Mysql Date Type
I've created a database for tracking our paper inventory. Basically when an individual takes paper, or envelopes the quantity is entered into the database, along with some other items. This all works great.

I also have two fields that use the "Date" and "Time" types for holding the date and time of the initial transaction.

I've created a seperate php script that we will use for our "end of month" reports. The script goes through and adds up the cost of each "purchase" between a specified time frame (1 may thru 31 may for example).

This script works for me as long as I perform my query with my condition formated as such:

---

Output Multiple Records From Start Date To End Date?
Firstly I'm using MySQL 5 on Win2003.

I have a DB table that has multple columns, but the 2 columns I am interested in are DATE_START and DATE_END. So I'll have many records in the DB and the values in these fields will be anything like:

DATE_START=2006-10-23
DATE_END=2006-10-26

Or if its a 1 day thing it will be sumin like:

DATE_START=2006-10-23
DATE_END=2006-10-23

Is there anyway I can output multiple records from the DB for that one record between those dates? For example, the first record above would output:

TITLE | SUMMARY | 2006-10-23
TITLE | SUMMARY | 2006-10-24
TITLE | SUMMARY | 2006-10-25
TITLE | SUMMARY | 2006-10-26

So basically I get a loop of all dates between the start and end date?

Before anyone asks, no I can't loop through this in the code! I have a calendar, and all the dates are created and I just need to populate these dates with the records... and I won't go into detail, but without querying every day, this is the best way by far to do it... just need to know if it can be done?

---

Select Date Only From Date Time Field
if I have "Date Time" field , How can I get the Date only from this field.

---

Latest Date Entry + Future Date
My requirement is data to be added to database depending on dates. Like If the user wants to add data for the next 10 days,the data should be added with a corresponding column containing the next 10 days' dates.

Then if he logs in tomorrow and wishes to add data for another 100 days,it should find the last date added and then add data with the next 100 dates starting from that date.

Also I would want to display these dates to the user.So can you suggest which type to be used for database field and also the date functions(mysql as well as php) to be achieved.

Let me put it more clearly.

"select the last date(which might be future dates) entered in the database";

If the date is Oct10 and the user wants to add entries for Oct 11,12 13

"insert into table values('data','oct11')"
......
......
likewise for Oct12 and 13

The format I need is just year,month and day.No time is required.

---

Date/time Field Update Only Date
I have a field(tmMessage) in a table, it's a date/time struct.
it looks like "2003-09-21 15:52:35"

All I want to do is change the date in this field.
I want the time to stay the same.

---

Calculate Days Between Current Date And Date
I need to calculate the days between the current day and the date stored in a date column. I saw the datediff() command, but that only works with mysql 4.1.1. I am running 3.23.58. How can I do this?

---

Date/time Field Update Only Date
I have a field(tmMessage) in a table, it's a date/time struct.
it looks like "2003-09-21 15:52:35"

All I want to do is change the date in this field.
I want the time to stay the same.

---

Updating A Date From Another Date In The Same Table.
I'm having trouble coming up with the correct SQL syntax to update a date field from another date field in the same table.

Both fields are DATE types.

I want to add 2 months to the date in the final_date field and update the keeplive_exp field with that total.

So if final_date has 2007-05-04, keeplive_exp needs to update to 2007-07-04.

---

Date Range Without Date Column
Suppose I have a table that says on which days I should eat certain foods:

+----+-------------+-----+-----+-----+-----+-----+-----+-----+-----+
| Id | Name | Qty | Mon | Tue | Wed | Thu | Fri | Sat | Sun |
+----+-------------+-----+-----+-----+-----+-----+-----+-----+-----+
| 1 | Carrots | 3 | T | T | T | T | T | T | T |
| 2 | Cauliflower | 1 | T | T | T | T | T | T | T |
| 3 | Broccoli | 2 | T | T | T | T | T | T | T |
| 4 | Peas | 10 | F | F | F | F | F | T | T |
| 5 | Potatoes | 1 | T | T | T | F | F | F | F |
+----+-------------+-----+-----+-----+-----+-----+-----+-----+-----+
And suppose this is the calendar:

August 2006
MonTueWedThuFriSatSun
123456
78910111213
14151617181920
21222324252627
28293031
For the days August 8 through August 10 inclusive, how can I figure out how much food I should eat with an SQL statement?

The answer should be:
9 carrots
3 cauliflowers
6 broccoli
2 potatoes

---

How To Add Date Based On Another Date In Record?
I would like to retreive only the number of rows while sum(field1) < 20. Is this possible?
If my table have the following values :

1
4
6
8
5

It only retrieves the first 4 rows. sum(field1)=19

---

Date Query (how Do I Get Everything With A Date In 7 Days?)
the goal is a reminder email to people who have a workshop coming up in 7 days.

So, workshop date is 2006-05-25.
The reminder email should go out on 2006-05-18

I guess mysql query returns all records where (Date column - 7 days) = current date. Right?

But what does teh QUERY look like? And how does it accomodate if today is 2006-05-28 and the upcoming workshop is 2006-06-04 (so month changes over the 7 day period).

---

Convert Date String Into Date
I have created a database using data in a text file. Unfortunately the date column in the text file returns the date as day month date (e.g. Mon Nov 13). How do I convert these dates into the mysql format of YYYY_mm_dd ?

---

Date Range From A Single Date
I have a football fixtures table with a date of the fixture stored with the fixture.
I want to display this fixture on my website for (say) 3 days before the actual fixture date and (say) 3 days after the actual fixture date.

Is this possible from within a single query? I think this might have something to do with the DATEADD/DATESUB functions, but I can't work out how to use these in the WHERE clause of my query.

---

Date Range On Date Field
I have a table that has birthday dates stored as a DATE field. How would I get all the rows returned based on a min years old and a max years old...Let's say...
My form has....

Display Rows by birthdate in years....

Show between (23 years old) AND (36 years old) from the current month/day/year

---

Adding A &quot;date Added/date Modified&quot; Column?
I want to have a column in my table that is automatically populated with the current date/time when record is inserted? Hopefully there is an easy way to do this that I don't know about.

---

Select From Date To Date
i need a query that selects from date to date in the end the query will be weekly top 5 tutorials and i would like it every sunday to automaticly change the date in the query so i never have to do it.

---

Getting Date Using Date Format
PHP Code:
 SELECT *, DATE_FORMAT(date, '%w-%M-%Y') AS datevalue FROM members where username = '$_SESSION[username]' 

I have that code to select from my database, it all works fine and I can view all entries. But the date still prints in the format it was stored.

---

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

---