[RESOLVED] GROUP BY problem

[raj_2006]

Hi all,

I have a order table where the datatype of date_purchased is datetime.

date_purchased

2007-01-20 10:34:39
2007-01-20 10:35:22
............................
............................

2007-01-21 4:35:22
............................

I want to display the order details of a particular date....... say for 20th jan how many and what orders has been added.So while I am using GROUP BY then its not working...as the time is different

Is there any other way to groupby a set of records of a particular date.

Please suggest me.......Thanks.......Raj

[russell]

what dbms?

[NightShift58]

From the PHP forum, I remember that Raj was using MySQL.

Would "GROUP BY substring(date_purchased,1,10)" work for you?

<?php
$select_date = "2007-01-20";
$sql = "SELECT substring(date_purchased,1,10) AS detail_date, count(*) FROM order_table ";
$sql .= " WHERE purchased_date like '$select_date%' ";
$sql .= " GROUP BY 1 ";
?>

[chazzy]

NightShift - You can't convert like that. he's got a date/time type, not a string.

raj, what happens you use date_format(date_column,'%m-%d') to extract just the day of the month and the month? you could format it to match that and group it on that. you will probably want to create an index though.

[GoldCoast_Nerd]

U need to pass the date as a variable to your query maybe from a drop down box or any way u plz. then just run a simple select statement to get your results as per date selected. so u go,

@date varchar[20] (that's ur passed variable)

SELECT Order_Details, etc, etc (column names u wannna select)
FROM table name
WHERE DATE = @date
ORDER BY DATE DESC

that will get all ur results on that day, in a decending order according to time. hope this works for u. :D

[NightShift58]

NightShift - You can't convert like that. he's got a date/time type, not a string. You can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:

* As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A “relaxed” syntax is allowed: Any punctuation character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', and '98@12@31 11^30^45' are equivalent.

* As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed” syntax is allowed here, too. For example, '98-12-31', '98.12.31', '98/12/31', and '98@12@31' are equivalent.

* As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '19970523091528' and '970523091528' are interpreted as '1997-05-23 09:15:28', but '971122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.

* As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '19970523' and '970523' are interpreted as '1997-05-23', but '971332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.See: http://dev.mysql.com/doc/refman/4.1/en/datetime.html for more information.

The proof is there... Try it...

[chazzy]

you still need to use str_to_date for it to recognize it as a date/time type. you also can't use % to find it like you would a varchar.

[NightShift58]

you still need to use str_to_date for it to recognize it as a date/time type. you also can't use % to find it like you would a varchar.That's the beauty and the magic of MySQL date/datetime/timestamp: you don't! I've been using this possibility since 1999.

Wonderful things like ... LIKE '2007%' for just the year or LIKE '2007-01-%' for year and month instead of BETWEEN/AND.

[raj_2006]

hi

Thanks to all for your contribution and also sorry to reply in late.....

I have used substring funtion and its highly ok.I mean if i use LIKE '%2007%' its fetching the desired results.

Now another problem related to GROUP BY is there.Let me explain first.

orders table

orders_id shipdate

182 1169884800 // i have converted it into date using date()

173 1169884800

176 1169798400

179 1170230400


Now for the same shipdate i want the different orders_id(like 182 & 173).But using GROUP BY shipdate i am getting only one orders_id.Can i put the different orders_id in a array inside a single query?

Raj

[NightShift58]

GROUP BY, as the name implies, will group the rows. If you need individual rows, then GROU BY doesn't make sense - at first glance. It depends on what you want to achieve.

What is your selection criteria - aside from GROUP BY?

[raj_2006]

Aside from group by i want to fetch the orders_id(s) whose shipdate is same or different

eg:

on February 3rd,2007(lets say 1169884800 is equivalent to February 3rd,2007)

orders id :182 and 173

again on on February 4rth,2007(lets say 1169798400 is equivalent to February 3rd,2007)

orders id :176

again on on February 5th,2007(lets say 1170230400 is equivalent to February 3rd,2007)

orders id :179

This is the thing i am trying to do.

[NightShift58]

Aside from group by i want to fetch the orders_id(s) whose shipdate is same or differentSo you want all of them, which is what "same or different" means...

[raj_2006]

yes...exactly

[NightShift58]

Then, if you want all of them, don't GROUP BY...

GROUP BY is not a requirement!

[raj_2006]

If i dont do GROUP BY then its appearing in this way:

on February 3rd,2007(lets say 1169884800 is equivalent to February 3rd,2007)

orders id :182

on February 3rd,2007(lets say 1169884800 is equivalent to February 3rd,2007)

orders id :173

again on on February 4rth,2007(lets say 1169798400 is equivalent to February 3rd,2007)

orders id :176

again on on February 5th,2007(lets say 1170230400 is equivalent to February 3rd,2007)

orders id :179

I mean on same date 2 different ids(182,173) are appearing in 2 different rows....where i am trying to merge it as it is falling under same date

This is the problem without using group by...also if i use group by then only one id is getting(183)

[NightShift58]

I don't know which part I'm not understanding.

If you group them, you will not see them individually.
If you don't group them, you will see them individually.

Do want to sort them, perhaps? ORDER BY date?

[NightShift58]

I think that you need to analyze what it is that you want to achieve. Perhaps writing it on paper and trying to sort out what you want from the table. Something like a concept.

I think that you expect me or others on the forum to "guess" what you need. This isn't really what this is all about.

Once you know what you want to do, I would suggest some basic reading. At a miminum, this: http://dev.mysql.com/doc/refman/4.1/en/select.html

[raj_2006]

If you group them, you will not see them individually.
If you don't group them, you will see them individually.

I want to group them as well as I want to see them individually......is it possible?

This is the objective.

Now i think using group by it is not possible untill I am sending 2 dates or 1 date to the sql using a form.

I want the o/p like this::

3/2/2007(1169884800 )

Order ID

182
173
***********
4/2/2007(1169798400 )

Order ID

176
***********
5/2/2007(1170230400 )

Order ID

179
***********

[NightShift58]

You'll have to use SORT BY in SQL to order the rows in the proper sequence and then, in your script, you'll have to do the breakdown yourself using varaible in a loop to keep track of changes in the date.

[raj_2006]

let me give it a try as per as your suggestion....... :) ...will let you know the result.

[NightShift58]

Here's skeleton script:<?php
$SELECTmonth = "2007-02";
$sql = "SELECT * FROM orders_table WHERE shipdate LIKE '$SELECTmonth%' ORDER BY shipdate, orders_id ";
$qry = mysql_query($sql) or die("SQL Error: $sql<br>" . mysql_error());

$old_date = "";

WHILE ($r=mysql_fetch_array($qry)) :
IF ($r['shipdate'] <> $old_date) :
echo "**************<br>";
echo $old_date . "<br>";
ENDIF;
echo $r['orders_id'] . "<br>";
ENDWHILE;
?>

[raj_2006]

that will not work in my case as my date is not constant...i mean i cant get the value of shipdate before the sql is running.

lets give you the explanation again.

Order table

orders_id shipdate
1 1169884800
2 1169884800
3 1169798400
4 1170230400

orders_products table

orders_product_id orders_id products

5 1 A
23 2 B
35 3 C
48 4 D


now my SQL:

$sql="select * from orders group by shipdate order by shipdate";
.......................
.......................
while($r=mysql_fetch_array($sql))
{
$orders_id=$r['orders_id'];
$shipdate=$r['shipdate'];
echo $shipdate;
..................................
..................................
$sql_pro="select * from orders_product where orders_id='$orders_id'";
......................................
.......................................
while($r=mysql_fetch_array($sql))
{
$products=$r['products'];
echo $products;
echo "*****************";
}
}
Now if i run the first query then the o/p is:

orders_id

1
3
4
as i am doing group by only 1 is getting.....and as a result while second query is running I am getting all products except product C

Hope I have make you understand about the problem.But I am surprised that while doing group by shipdate in the 1st query it should return 1 & 2 but its returning only 1.....this is the main problem to me.

My desired output should be:

1169884800

A
B
************
1169798400

C
************
1170230400

D

Thats it.......but also here i think either i have to use a form to send 2 dates or i have to change the table structure.Primarily i thought that group by will return 1 & 2 on the same shipdate.

IF you do group by shipdate you will get the 1st row.....is it sured enough?

[NightShift58]

<?php

$sql1 = "SELECT shipdate FROM orders GROUP BY shipdate ORDER BY shipdate ";
$qry1 = mysql_query($sql1) or die("SQL Error: $sql1<br>" . mysql_error());

WHILE ($r_date = mysql_fetch_array($qry1)) :
$thisDATE = $r_date['shipdate'];
echo $thisDATE . "<br>";

$sql2 = "SELECT orders_id FROM orders WHERE shipdate = '$thisDATE' ORDER BY orders_id ";
$qry2 = mysql_query($sql2) or die("SQL Error: $sql2<br>" . mysql_error());

WHILE ($r_order = mysql_fetch_array($qry2)) :
$thisORDER = $r_order['order_id'];
echo $r['orders_id'] . "<br>";

$sql3 = "SELECT * FROM orders_products WHERE orders_id = 'thisORDER' ";
$qry3 = mysql_query($sql3) or die("SQL Error: $sql3<br>" . mysql_error());

WHILE ($r_product = mysql_fetch_array($qry3)) :
$thisPRODUCT = $r_product['products'];
echo $thisPRODUCT . "<br>";
ENDWHILE;

ENDWHILE;

echo "**************<br>";

ENDWHILE;
?>

[NightShift58]

Or this:<?php
$sql1 = "SELECT shipdate, orders_id FROM orders GROUP BY shipdate, orders_id ORDER BY shipdate, orders_id ";
$qry1 = mysql_query($sql1) or die("SQL Error: $sql1<br>" . mysql_error());

$old_shipdate = "";
WHILE ($r_date = mysql_fetch_array($qry1)) :
$thisDATE = $r_date['shipdate'];

IF ($old_shipdate == "") :
$old_shipdate = $thisDATE;
ENDIF;

IF ($thisDATE <> $old_shipdate) :
echo "**********************"<br>;
echo $thisDATE . "<br>";
$old_shipdate = $thisDATE;
ENDIF;

$sql3 = "SELECT * FROM orders_products WHERE orders_id = 'thisORDER' ";
$qry3 = mysql_query($sql3) or die("SQL Error: $sql3<br>" . mysql_error());

WHILE ($r_product = mysql_fetch_array($qry3)) :
$thisPRODUCT = $r_product['products'];
echo $thisPRODUCT . "<br>";
ENDWHILE;

ENDWHILE;
?>

[raj_2006]

Hi,

Its working.... :D .I have not thought of the 2nd sql between 1st and 3rd where the orders_id are pulling out according to the grouped shipdates(frm the 1st sql).....

I just now want to tear my hairs for a simple thing... :D

Thanks mate once again for your beautiful help......Thanks

Luv
Raj

[NightShift58]

You're welcome!