Count The Hits For Week And Month In One Query?
I have this query to count the amount of total clicks for a whole week, but i would also like to know the total amount of clicks for the whole month. Is it possible to do both of them with one query? Or do i really need to make 2 seperate querys for that?
SELECT date FROM clicks
WHERE date >= '$timestamps[0]' and date <= '$timestamps[6]'
AND link_id = '{$row['ad_linkid']}'
View Complete Forum Thread with Replies
Related Forum Messages:
Sql Query For Count Records Of Current Week
I have a registration form that saves the registration date on a datetime column. I would like make a query to know how many records are in the table that where for the current week. For example, I'm on week 25 and I want to know how many records I have for this week without the need of specifying the from and to dates. Is there a way to do this?
View Replies !
Single Query For No Of Hits "Today", "This Month", "This Year"
Is it possible to construct a single query that fetches the no of pageviews "today", "this month", "this year" and "total since start"? The table is very simple, one field for Date (YYYY-MM-DD) and one for the no of pageviews this day. If not, would it be much slower/faster to divide the query into 4 different queries?
View Replies !
Count And Group By Month In 1 Query For A Date Range?
I have a table that has employees work history on it. Basically the only thing I am worried about is the start and end date. My boss wants a report that charts out the quantity of workers for the past 12 months group by the month. For example Employee--Start Date--End Date Charles-----2008-6-3---2009-2-1 Vicky-------2009-1-1---Present So in this case there would be a quantity of 1 from June 08 to December 08. Then their would be a quantity of 2 from Jan 09 to Feb 09. And Vicky would also be counted for March since she is still working. I can do this by creating PHP functions and putting it all together, but I was just wondering if there was a way to do this all in 1 query.
View Replies !
Week Number In Specific Month
I have a problem regarding the week number in specific month..... i would like to get the week number in that certain month by just providing the date...... for example : 2007-10-07 - week 1 (oct) 2007-10-14 - week 2 (oct) but i want it to return me 1 when i provide (2007-10-07 to 2007-10-13) and return me 2 when i provide (2007-10-14o 2007-10-20) ..... and so on.... it is possible ?
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 !
Count 1 Week Interval
I'm having some trouble patching up my referrer script. I want my PHP script to tell me how many visitors I had the following week, up to today. I've wrote the monthly, current and yesterday bits, but I can't manage to create a weekly one. can someone help out? // yesterday $yesterday = date("Y-m-d",mktime(0,0,0,date("m") ,date("d")-1,date("Y"))); $sql_yesterday_command='SELECT count(*) as total FROM visits where date="'.$yesterday.'"'; $query_yesterday=mysql_query($sql_yesterday_command, $connect); $fetch_array_yesterday=mysql_fetch_array($query_yesterday); $visitors_yesterday=$fetch_array_yesterday['total']; // this month $sql_month_command='SELECT count(*) as total FROM visits where date like "'.date("Y").'-'.date("m").'-%"'; $query_month=mysql_query($sql_month_command, $connect); $fetch_array_month=mysql_fetch_array($query_month); $visitors_month=$fetch_array_month['total'];
View Replies !
Retrieving Months And News Count From That Month
I'm creating a news archive page. Here is a trimmed down version of my news table: news_id author_id title maintext date_added (datetime) What I want to do in one query is get a list of all the months in a given year and a count of how many news stories were added in each month. Off the top of my head I could just think of selecting distinct month from date_added and counting the news items in that month and given year. Perhaps there is a better way though as I realise the distinct function isn't very efficient.
View Replies !
Query For Last Week
I hope this is the right group for this: I am trying to query a db to return only values for the current week. My query currently look slike this: "SELECT {$iLabel[0]} FROM {$iTable} WHERE {$iLabel[0]} <= now() AND {$iLabel[0]} >= date_sub(now(), INTERVAL 1 WEEK) GROUP BY floor(unix_timestamp({$iLabel[0]})/{$AvgTime}) ORDER BY unix_timestamp({$iLabel[0]}) ASC",$iConnection); This returns a full week's worth of data. How can I get it to only return the current weeks data (say from Sunday on)?
View Replies !
Change Week Start Day When Selecting Week
I am building a tip tracking database to be used by restaurant employees. I need to allow users to choose their scheduling/pay period start day as they tend to vary in this business. I will be selecting shifts by week for each user and want to be able to define the start day of a week for any given user while querying. Is this something that can be done with MySQL?
View Replies !
Query Dates Within 1 Week From A Date
I'm fairly new to PHP and have done some basic work with mysql but am not sure how to approach this. I'm trying to figure out how to create a query with PHP to get all rows with a datetime from 1 week ago to the current timedate. Note: I'm also not sure how to find a date for 1 week ago with PHP. Any help would be great.
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 !
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 !
Query For Month Data Based On Occurance Against Master List
I have a query running nicely. Now I'm trying to expand it. It pulls the data from a table based on matching the id with another table and part of the grouping is by month and 2 other criteria. Now I'm trying to get the data pulled by month to pull only the info where the id matches the master file AND the FRANID's pulled are the same for each month. I'm trying to get a comparisson across times, but not every month has all FRANID's, depending on when they were entered and I ony want data that exists with matches over set months, starting for now with 12, perhaps also for 6 and 3 next time. I've simplified what I'm doing above, but it is detailed below.....
View Replies !
Trying To Get Total Count Plus Count From WHERE Clause In One Query
I am fairly familiar with mysql, have been using it with php for about a year now for my development work. One query I am stuck on is the following. I am doing reporting, and need to find the count from a WHERE clause compared to the total count for that group. For the sake of an example, let's say I have a prize table that contains a complete list of prizes. There is a column to list if the prize has been won or not (prizeUsed - Y or N) and another that has the prize name (prizeName - string). I want the final result to list something like: Total Prizes ____________________ 100 of 850 ......
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 !
Number Of Hits
i got one hit field in one of my table, that is when user click, the hit will increase once so can i write the script like below: <? $hit=mysql_query("UPDATE pa_hit SET hit+=1"); ?>
View Replies !
Tracking Unique Hits
I've got a simple database to track who looks at any given sale item on my site. The table is set up as such: CREATE TABLE stats ( stats_id int(11) NOT NULL auto_increment, stats_create int(11) NOT NULL default 0, sale_id int(11) NOT NULL default 0, user_id int(11) NOT NULL default 0, user_ip int(11) NOT NULL default 0, PRIMARY KEY (stats_id) ); The `stats_create` field is a unix timestamp of when any given user looks at the sale. Currently, I came up with this to get the total number of hits over the last 7 days (today included) for any given $sale_id: SELECT DATEDIFF(CURDATE(), FROM_UNIXTIME(stats_create)) AS days_old, COUNT(stats_id) AS hits FROM stats WHERE sale_id = $sale_id AND DATEDIFF(CURDATE(), FROM_UNIXTIME(stats_create)) < 7 GROUP BY days_old What I would like to do is to come up with a query that will select the number of unique hits over the last seven days. In other words, if user #2 has looked at this sale previously, their visit will be counted either 0 or 1 times (1 for the first time they visit--which would show up if that falls within the 7-day range; but 0 for all times thereafter). But as you can see, I have no idea how to formulate such a query.
View Replies !
Tracking Number Of Hits
For a project I am working on I need to track the number of hits per profile view. Originally I just stored the hit count in the profile table. Except now the client wants to be able to view reports showing number of hits between any two dates for any profile. I really can't think of a good way to do this. This is all I can come up with for a table structure: profile_hits -date -hit_count -profile_id The problem is I will be inserting a LOT of records into this table. It will work but perhaps there is a better way of doing this?
View Replies !
Creating A Counter For 'hits/listens' For Each Mp3 File.
I am designing a site that is regulary updated with new podcasts. I would like to be able to have a counter next to each podcast showing how many hits/listens/plays/views each particular podcast has gotten. eg: podcast001.mp3 plays: 12 I have set up a column in a mysql database for each podcast called 'counter'. I then tried to make a php-javascript hybrid code with functions on onClick that went into and added one to the counter column of the appropriate row in the mysql database. This failed miserably. Turns out you cannot mix these things, one being client based, the other browser based. Is this the best way to approach the problem? Am I missing something really obvious? Is it possible to use awstats somehow? Is there a sneaky way of getting php code to act like java's onClick? .....
View Replies !
Query Count
I've seen on some sites that there is a way to total up the number of queries ran on a page, and the time it took to run them - as per the bottom of this page: Is this something which can only be done using PHP, or could I achieve the same result using ASP, by accessing something within MySQL?The reason I ask is that I have a site running MySQL on a Windows server, running IIS and ASP, and would like to set something up like the above.
View Replies !
Sum And Count In A Query
SELECT popu.REGION, sum(ritstat.collect), popu.Habit FROM ritstat Inner Join address ON ritstat.addrID = address.addrID Inner Join popu ON address.PostCode = popu.CP group by REGION
View Replies !
Help With Query Using Count
I have the following query, but for some reason I can't seem to get the price count part to work. What I want to do is the get the max and min price for each indivdiual product and I then want to count how many products of the same where found with these different prices. The only problem is that the count part seems to be giving me the wrong totals. For some it is giving me 783 when there is only 2 of the same products. Here is the query that I have. PHP select ides,merchantes.merid,merchant,price,min(price) as minprice,max(price) as maxprice,count(price) as pricecount,rand3,title,imageurl,brand,description,pm from merchants,prodsearches where prodsearches.merid=merchants.merid and app=Ƈ' and match (brand,category) against ('"games"' IN BOOLEAN MODE) group by brand,category order by random2 asc
View Replies !
SQL Count Query
I have 3 tables: j_photoparent (holds photo "parent" e..g "nature", "places") j_photocat (holds photo categories, e.g. "leaves", "snow", "london eye") j_photosmaster holds photo details The keys are as follows: j_photoparent.parentID j_photocat.catID j_photocat.parentID j_photosmaster.photoID j_photosmaster.catID I have a simple SQL statement to randomly select a photo category: SELECT jpc.catID, jpp.parentID FROM j_photocat jpc, j_photoparent jpp WHERE jpp.parentID = jpc.parentID ORDER BY RAND() LIMIT 1 What would be great though, would be if I could combine the SQL above, to also count how many photos in the "j_photosmaster" table for each randomly selected category. Could it be done in the same statement - or would I need to do two - the first one above and then this one: SELECT COUNT(*) FROM j_photosmaster WHERE CatID = &catID_FROM_ABOVE Could it be done with a sub-select?
View Replies !
Automatically Generate Text Excerpt For Text Near Keyword Hits?
I would like to generate an automatic text excerpt showing the text close to the first occurance of a keyword needle in a text haystack. For example: searching for keyword = "foo" in a table called Webpages in the column Content = "Yadda, yadda, yadda. Bla, bla, bla foo yadda yadda yaddda bla bla bla" would return a column called Excerpt = "...a, bla foo yadda yadd..." Is this best done with PHP after fetching the entire column Content, or will it be more efficient to do directly with MySQL? What would that SQL query look like?
View Replies !
Make A COUNT Query
I want to run a query on quite a big table, about 3,000,000 records. The query is quite simple SELECT COUNT(*) FROM TblName WHERE Something = 1 I am only interested to know whether there are at least 10,000 records that meet this condition, so counting the whole table and then comparing the result to 10,000 is overhead. Is there a way to write a query that would simply tell me if there are at least 10,000 (or any other number) records, and would stop executing/counting once it gets to 10,000?
View Replies !
MySQL Query COUNT
I need to create a sql statement that queries a mysql database for the top x amount of entries that include their name. Basically the site logs everytime they upload a file, and I want to see which users have the top number of uploads. Any ideas? Thanks in advanced to any help. Table: fileid filename submittername submitterip fileinfo filetype date
View Replies !
Query Count Return Zero
I'm trying to query a database searching for everytime an event happened in the past 24 hours. I'm using a count(eventid) to add up each event during an hour time. However, if an event didn't happen say for 2AM then 2AM isn't shown in the return. How can I get 2AM to show as a row, but with a count of 0 (zero) ?? Here is my qeury... select count(eventid), DATE_FORMAT(CONCAT(eventdate,' ',eventtime),'%Y%m%d%H') as hour, time_format(eventtime, '%H:00'), eventdate from event where sensorid = '2' and eventdate between '2005-01-19' and '2005-01-20' and DATE_FORMAT(CONCAT(eventdate,' ',eventtime),'%Y%m%d%H%i%s') >= '20050119150018' group by hour order by hour desc;
View Replies !
Query For Count And Data
My sql statement looks like this: Code: $query = "SELECT count(*) as Assisted, Name FROM (lobby inner join users on lobby.Assisted_by = users.UserID) where Assisted_Time >= '$var{From_Date}' and Assisted_Time < '$var{From_Date}' + 1 $Branch group by Assisted_By"; What i really want to do is to extract the actual data from lobby table for addtime, lastname, and firstname. then in my code i will compare all of these values and if they are the same, i will skip fetching the data. Can i get the actual data from those collumns while i am getting the count(*) for my graph, all in one sql statement?
View Replies !
Count Occurence Twice In One Query?
I have a simple database where I've got 2 columns "product" and "flag" where I want to count occurrences. "product" contains random product names and "flag" will have an "x" if flagged or anything except and "x" if not flagged. I currently use two queries as follows: SELECT product, COUNT(product) AS flags FROM `TABLE` WHERE flag = 'x' GROUP BY product ORDER BY flags DESC which gives a table: | product | flags | ------------------- | pear | 6 | | apple | 4 | | orange | 1 | ------------------- SELECT product, COUNT(product) AS noflags FROM `TABLE` WHERE flag != 'x' GROUP BY product ORDER BY noflags DESC which gives a table: | product | noflags | --------------------- | apple | 4 | | pear | 3 | | orange | 1 | --------------------- I want to combine these two queries so I am effectively counting occurrences twice to produce a table as follows: | product | flags | no flags | ------------------------------ | pear | 6 | 3 | | apple | 4 | 4 | | orange | 1 | 1 | ------------------------------
View Replies !
Simple Count Query
I am trying to use the below query to count the number of images held for each book. Unfortunately it is only returning the books with images. I would like all books returned even if they have no corresponding image. SELECT tblBooks.Title, tblBooks.Description, tblBooks.Author, tblBooks.Annotation, tblBooks.Collection, tblBooks.Price, tblBooks.SortID, tblBooks.Heading, Count( tblImage.ImageID ) AS CountImage FROM tblBooks INNER JOIN tblImage ON tblImage.BookID = tblBooks.BookID GROUP BY tblBooks.BookID
View Replies !
COUNT() 2 Tables In 1 Query
So I have these two queries: $result= mysql_query("SELECT YEAR(add_date), MONTH(add_date), COUNT(*) FROM clogsyn_track_main where user_id='$uid' GROUP BY YEAR(add_date),MONTH(add_date)"); $result2= mysql_query("SELECT YEAR(add_date), MONTH(add_date), COUNT(*) FROM clogsyn_track_pop where user_id='$uid' GROUP BY YEAR(add_date),MONTH(add_date)"); They output data like Month Year, Total Views So I can see how many total times a page was visited that month for a specific user_id. The problem is I'm tracking 2 sections of the site "main" and "pop" which pop is a unique page that pops up when something is clicked. I tried EVERY way to join them together but the count(user_id) doing a LEFT JOIN kept adding the count()'s from both tables together. Is it not possible to do a count() on two tables in 1 query? Here's one of the many combined I tried. $sql = "SELECT a.user_id,b.user_id,YEAR(a.add_date),MONTH(a.add_date),COUNT(a.add_date),COUNT (b.add_date) FROM clogsyn_track_main a LEFT JOIN clogsyn_track_pop b ON a.user_id=b.user_id AND YEAR(a.add_date)=YEAR(b.add_date) AND MONTH(a.add_date)=MONTH(b.add_date) WHERE a.user_id='$uid' GROUP BY YEAR(a.add_date),MONTH(a.add_date)"; Can anyone help me here or is it not possible to do it in 1 query?
View Replies !
Query To Return Two Count()s
I'm trying to figure out a better way of doing this: SELECT (SELECT COUNT(*) FROM GuestEvents WHERE EventID = 1 AND EventStatus = 'y' AND UserID = 'bob') AS Event1, (SELECT COUNT(*) FROM GuestEvents WHERE EventID = 2 AND EventStatus = 'y' AND UserID = 'bob') AS Event2 As you can see it's quite repetitive, I wondered if there isn't something like this which could optimise it a bit: SELECT COUNT(EventID=1) AS Event1, COUNT(EventID=2) AS Event2 FROM GuestEvents WHERE EventStatus = 'y' AND UserID = 'bob'
View Replies !
Count Query Problem
I want to count users by their status, following is the code which I am using currently, instead of using four queries I want to convert this code in one query....
View Replies !
WHERE COUNT MySQL Query
PHP $queryA = " SELECT soundcard_co, soundcard_model, count(soundcard_model) as count_model, AVG(value_num) AS value FROM reviews GROUP BY soundcard_model ORDER BY value DESC LIMIT 10"; Okay, there's my MySQL query. I have a crappy little review script that lets people review home recording sound cards. This script will display 10 sound cards with the highest average score for overall value. As you can tell, this selects the the company, model, and average overall value rating of sound cards that have been reviewed on my site. It puts them in descending order and limits the results to the top 10. I've found that these results are easily skewed when a particular sound card gets only one review. I don't want a single person to have the power to put a sound card on top of my ratings. So, I want to select sound cards from the database that have had at least 3 votes. While I could come up with a php solution, I'd prefer to use MySQL. I have tried this: PHP $queryA = " SELECT soundcard_co, soundcard_model, count(soundcard_model) as count_model, AVG(value_num) AS value FROM reviews WHERE COUNT(soundcard_model)>2 GROUP BY soundcard_model ORDER BY value DESC "; But I keep getting a "Invalid use of group function" error.
View Replies !
Can I COUNT Multiple Things In One Query?
SELECT COUNT(price) FROM vehicles WHERE country = 'GBR' AND price > '0' AND price <= '2499'; SELECT COUNT(price) FROM vehicles WHERE country = 'GBR' AND price > '2500' AND price <= '4999'; SELECT COUNT(price) FROM vehicles WHERE country = 'GBR' AND price > '5000' AND price <= '10000'; Consider the above queries, these are currently used to pull out the amount of vehicles failing into a particular price range and the output would look something like this 0 - 2499 (123) 2500 - 4999 (345) 5000 - 10000 (1234) Now, it should be noted that there are more than 3 categories (often 10 or 15), and it is not just for price, we also do years (in decades at the moment, but may not always be). The ranges for the categories can and will change, and so the queries are built by PHP. The ranges in the above example would be supplied like this: $range_arr['price'] = array('0','2500','5000','1000' ..... Anyway, this is just background
View Replies !
Need Single COUNT Value For Complex Query
I have a moderately complex query and I want to page the results, limiting to 50 on a page. I know that in order to do so, I need to know the total number of records so that I know whether there are more records remaining so I can show a 'NEXT PAGE' link. I'm having trouble constructing a COUNT query from my original query because the query accesses 4 tables, two of which are many-to-one association tables. Here is the original query, without a LIMIT clause:
View Replies !
Query - Count With Date Intervals
I have this: select count(*) from visitors where visitordate >= '2008-01-01' AND visitordate >= '2008-01-08' Now this gives me a count of how many visitors that week visited my site but I want something more automatic. I do not know which functions to use to make this logic below happen: Start with year base (ie:2008) increment by week number (ie:upto 52) and determine how many visitors each week visited the site. I wish this worked: select count(*) from visitors where visitordate = week(1), then week(2), etc.
View Replies !
Outer Join Count Query
I want to create an outer join along with usage of group by & count... So, I want the rows with count=0 to be part of the result... Select a.area, count(l.id) as no_listing from area a left join listing l on a.area=l.area group by a.area order by a.area; Note that an area can have multiple listings... Now, this query returns me only the areas with some positive count of listings, in spite of using a left join...
View Replies !
Count Query With Conditional Exclusion By Value?
Below is the first bit of code which is fine (ignore the php syntax) SELECT COUNT(*) FROM $wpdb->posts p WHERE p.post_parent = '{$page->ID}' AND p.post_status = 'publish' AND p.post_type = 'page' This is in a Wordpress environment if it isn't obvious. The meta table just lets you assign user defined key/value pairs to posts. Now if you want to positively check against meta values the Wordpress website recommends something like this: SELECT COUNT(*) FROM $wpdb->posts p, $wpdb->postmeta m WHERE p.post_parent = '{$page->ID}' AND p.post_status = 'publish' AND p.ID = m.post_id AND p.post_type = 'page' AND m.meta_key = 'tag' AND m.meta_value = 'email' This is easy, just more ANDs as you're looking for posts with a specific key/value pair. But what we need in our case is to exclude the posts with the specific key value pair! The key/value pair is sparse and it isn't practical to require the opposite for every post. The odd thing is, just to get started...
View Replies !
Retrieve Everything AND Count Rows In One Query
set rsminmax = con.execute("SELECT * FROM `minirules_minmax` where RuleId = '" & contractId & "'") set rsminmax2 = con.execute("SELECT COUNT(*) FROM `minirules_minmax` where RuleId = '" & contractId & "'") Is there any way to do this in one SQL query?
View Replies !
SQL Query To Include Count From Associated Table
I am having a tough time coming up with the sql for the statement but the long and short of it is this: Two Tables: Table 1 is a group - Table 2 is a list of items that belong to a group from table 1. I want to query all (*) information from table 1 AND get the count of the number of associated items from that group from table 2 in that same query.
View Replies !
Help With Query: Order By Association Count
Allright, here are my tables: Posts: id | title | text Comments id | post_id | name | comment Basically one post may have many comments. Now, how would I select the 10 posts with most comments, and if only 5 posts contained comments (but there are in fact 10 posts) how could I make this query still return a total of 10 posts?
View Replies !
|
TRACKER
