Articles
Windows FAQ
"ucmapi.exe" Process on Windows 7 - What is the "ucmapi.exe" pro...
"FMAPP.exe" Process on Windows 7 - What is the "FMAPP.exe" proce...
"soffice.exe" Process on Windows 7 - What is the "soffice.exe" p...
"Pulse.exe" Process on Windows 7 - What is the "Pulse.exe" proce...
"jusched.exe" Process on Windows 7 - What is the "jusched.exe" p...
Who's Online
11 user(s) are online (3 user(s) are browsing Forum)

Members: 0
Guests: 11

more...

Browsing this Thread:   1 Anonymous Users





How to group mysql based on items and today date
Home away from home
Joined:
2007/11/4 20:11
Group:
Registered Users
Posts: 334
Level : 17; EXP : 7
HP : 0 / 401
MP : 111 / 10827
Offline
Q:I have a database that store transaction logs, I would like to count all the logs for that day and group them based on prod_id

ID | PROD_ID | DATE | PERSON |
+------+---------+------------+--------+
| 1 | 2 | 1400137633 | 1 |
| 2 | 2 | 1400137666 | 1 |
| 3 | 3 | 1400137125 | 2 |
| 4 | 4 | 1400137563 | 1 |
| 5 | 2 | 1400137425 | 2 |
| 6 | 3 | 1400137336 | 1 |

MYSQL CODE:

$q = 'SELECT count(ID) as count
FROM PRODUCTS
WHERE PERSON ='.$db->qstr($person).'
AND DATE(FROM_UNIXTIME(DATE)) = DATE(NOW())';

so what I get is the number of items for the given date. Since the date is the same as all other entries. however I would like to group the items by prod_id, I tried GROUP BY PROD_ID but that did not give me what I want. I would like it to group if the PROD_ID is multiple and the date is the same display as one entry while still count the others

so here I should get an output ($Person = 1).... 2+2+2=1 +3 +4 so total should be 3




An:
Example:

SELECT count( distinct PROD_ID ) as count
FROM PRODUCTS
WHERE PERSON = 1 -- <---- change this with relevant variable
AND DATE( FROM_UNIXTIME (DATE ) ) = curdate();

And I suggest you to use Prepared Statement to bind values.

Posted on: 2015/11/23 23:54
Transfer the post to other applications Transfer






You can view topic.
You cannot start a new topic.
You cannot reply to posts.
You cannot edit your posts.
You cannot delete your posts.
You cannot add new polls.
You cannot vote in polls.
You cannot attach files to posts.
You cannot post without approval.

[Advanced Search]


Copyright (c) 2015 FYIcenter.com
Search
Main Menu
Login
Username:

Password:

Remember me



Lost Password?

Register now!