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 Complete Forum Thread with Replies
Related Forum Messages:
Mysql Is Giving The Previous Month, Not This Month, Strange!
PHP Code: $myquery = @mysql_query("select year(sendon) as yr, month(sendon) as mth, count(*) as hits from ebook_user group by yr, mth"); while ($myrow = mysql_fetch_array($myquery)) { $mymonth = $myrow['mth']; $myyear = $myrow['yr']; $myhits = $myrow['hits']; print '<td bgcolor="#E7E7E5">'.date("M", mktime(0, 0, 0, $mymonth, 0, $myyear)).' '.$myyear.' </td><td bgcolor="#E7E7E5"> '.$myhits.'</td>' } The above code is printing the previous month instead of current month ... can any one help?
View Replies !
Month By Month Count Of Records
I need to generate record counts for each month in a year, just one year at a time, but a full year at a glance. I am thinking 12 hits to the DB is perhaps not the most idea way to do it, is there a way to rip this out in one go?
View Replies !
Collecting Month By Month Results
does anyone have thoughts on how to do queries that produce date bins? I do things like DATE_FORMAT( created_time , '%M %Y' ) AS date and then group by date thing is that when there is nothing in a particular month, no row is returned ... So a query like this: Code:
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 !
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 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 !
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 !
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 !
Different Month
I have a table that has a column containing date and time information with format of 0000-00-00 00:00:00, I want to know how to write query to get rows that are in 2006 Jan, Feb, Mar, ..., return information on a monthly period. I use PHP to allow users to input the 2006-01, 2006-02, in my code, I check if value is 2006-01, then I set two variables to '2006-01-01' and '2006-01-31'; if the value is 2006-02, then set the values to '2006-02-01' and '2006-02-28'. But I hope there is better way instead of writing if condition one by one.
View Replies !
How To Add One Month To Now()?
Within my PHP5 application I need to set an expiry date when inserting/updating into the MySQL database - but honestly I've got no clue how... Specifically - it needs to be one month from the day of the insert/update (thus one month past Now()) Currently I've got the following working fine: UPDATE item SET expdate=Now() WHERE pid=1; Obviously this set the expdate to the current date, I've tried doing Now()+1 (didn't work), was looking into ADDDATE (couldn't get it to work), etc. How, in MySQL, can I add 1 month to Now()?
View Replies !
Get The Next Month , Or The Month Before
I need to get next day/month, based on the current date. Cannot find functions for dates range. today : 20090311 need to get the next day: 20090312 today is 20091231 need to return 20100101 SELECT ADDDATE( current_date, INTERVAL 1 DAY); today : 20090311 need to return day before: 20090310 today is 20100101 need to return 20091231 SELECT ADDDATE( current_date, INTERVAL -1 DAY); dates range today : 20090311 need to return strdate: 20090401 enddate: 20090430 today is 20091212 need to return strdate: 20100101 enddate: 20100131 today : 20090311 need to return strdate: 20090201 enddate: 20090228 today is 20100105 need to return strdate: 20091201 enddate: 20091231
View Replies !
Last Month Value?
Is there a simple query to return the previous month range? For instance, on October, I would like to run a report for the month of September. So, I would just like to get the date range from September 1st 2004 to September 30th 2004. This would be helpful as every month's number of days varies and also in case, it is a leap year.
View Replies !
MONTH And DAY
I have a table containing a Date field Birthday. I'd like to find all the birthdays today. In Access: select * from Alumni where (month([Birthdate])=2) and (day([Birthdate])=1); This errors out in MySql. I've tried month(Birthdate), month('Birthdate'), month[Birthdate], etc. - all fail.
View Replies !
Last Month
I was wondering if people had a better way of doing this, I am just trying to set an SQL way of doing a time span for last month. Below is an example of how I would use this. date between Dateadd(dd,-Day(getdate())+1,DateAdd(m,-1,GetDate())) and Dateadd(dd,-Day(getdate()),DateAdd(m,0,GetDate())) This will do it, but I was wondering if there is an easier way?
View Replies !
MySQL In 1 Month
I do some website design here and there occasionally for a few clients and I've got a new one that wants a database with their website that will display pricelists displayed for their products and they want the ability to have individual logins for their customers. I'm more of a designer than a programmer and I've never really done anything complex with a database before, and certainly nothing with MySQL. I had someone who I thought was going to do the database part with me, but he no longer wants to do it. So I was wondering if its possible to learn MySQL in a motnh or so well enough to allow it to interface with a website and do what this client is suggesting? Does anyone have any helpful tips, tutorials, books etc that would help me do this fairly quickly? Being a full time college student taking summer classes I already feel swamped! But I want to try to get this working if possible.
View Replies !
Last Month Of Data
I am trying to limit my query to the last month of data. Below is the limiter that I have used. The query is accepted by the server, and returns data, but does not limit the data. Any suggestions as to how to correct this? where issueddate > date_sub(current_date,interval 1 month)
View Replies !
Order By Month
How can one "ORDER BY month"? I wish to select a heap of distinct months and list them in the correct order eg: January, February, March etc... I experimented with the month(month) function but to no avail. I currently have the month stored as 'January' for example using PHP date format date("F");
View Replies !
How To Get The Last Saturday Of Month?
I need to query on the date in one of my table but I need to calculate the last saturday of month first. How do I do that in SQL statement? Specifically, How do I find the last saturday of month given the date (as SQL parameter) in SQL statement in MySQL?
View Replies !
This Or Next Month From Today
I want to select records which go from a certain date, until the end of next month. I struggled with DATE_ADD, but I think it gets more complicated than needed. This is what I made: SELECT * FROM table WHERE cat='1' AND ( record_date >= $test_date ) AND ( ( YEAR(record_date) = YEAR($test_date) AND ( MONTH(record_date) = MONTH($test_date) OR MONTH(record_date) = MONTH(DATE_ADD($test_date,INTERVAL 1 MONTH)) ) ) OR ( YEAR(record_date) = (YEAR($testdatum) + 1) AND MONTH(record_date) = MONTH(DATE_ADD($test_date,INTERVAL 1 MONTH)) ) )
View Replies !
How To The Numeric Value Of The Month?
i'm a table in which i store the month as full month i.e March,October And i'm going to fetch the data from other table bases on the month value gets from the first table. I'm a date field in the second table which i'm going to check the rows for greater than the fetched month.. How i can handle this. If i can get the values as 10,4 for October,March respectively then i can easily get the data from the other table.
View Replies !
Get Month From Database?
How can I query a database to get all entries from the month of July 08 using get from the browser bar? $SQL = "SELECT field1, field2 from dbtable WHERE the_date=".$_GET['july_string_from_get'];
View Replies !
Update Month + 1
I have a table with some data fields. I wish to update the table in order to have the data entries as new_data= old_data + 1 month.
View Replies !
Birthday Query Per Month
I want to output all the birthday celebrants for every month. YYYY-MM-DD is the format is my birthday. Not just the present month birthday celebrant but, per month. I try to use Select * from tblSample where birthday between '' and ''; But no success for i don't have the start date and end date of every month.
View Replies !
Date Format With Month Name
if it is possible to insert into a mySQL db a date format formatted like so: 11-Oct-06. I would like to save myself any tedious string processing tasks (I know I could convert it with PHP...).
View Replies !
Determine Previous Month
basically I need to display a list of "unpaid" statements for the previous Month, no matter what day the search/request is made on. I know MySQL has the DATE_SUB function where I can subtract 1 Month from the current date, but will that subtract the date exactly 1 month to the day ? Or will it simply give me the Month as I need ? What happens if the search takes place on the 31st of a month (for example: July), yet the previous month only has 30 days (June). Will it roll back to the previous month correctly or think that the date is 31st June or try to roll back further until it finds another month which has 31 days ?
View Replies !
Number Of Records Per-Month
i have got a script that log all downloads (daily) in a database. I would like to know how to query that database, to get the number of records per month. My records look like this date ip 2006-01-01 200.1.157.11 2006-01-01 192.168.10.3 2006-02-05 198.10.10.2 etc needed output 01 2 02 1
View Replies !
Rows In Past Month
I know I'm not writing this SQL command properly, please correct it for me. sql Code: Original - sql Code SELECT newsid,title,posted,content FROM `news` WHERE NOW() - `posted` < 2678400 SELECT newsid,title,posted,content FROM `news` WHERE NOW() - `posted` < 2678400
View Replies !
Current MONTH And YEAR
Having an event-calendar table, and an attribute event_date, How can i retrieve the events of the current month and current year based on that field? .....
View Replies !
Display Month As April Instead Of 4
I am trying to return the current month, by the month's actual name rather than the number of month. SELECT MONTH(CURRENT_DATE); I want to see April instead of 4. I was thinking I need to add DATE_FORMAT but I'm not sure what the syntax should be.
View Replies !
Filtering The Birthdays By Month
I'm working on a procedure about the birthdays of the employees, but I'm having a hard time making it appear in the browser.. I'm not sure about the right query in filtering the b-days by month.
View Replies !
|
TRACKER
