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 Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
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;
Why Does This Count Return 1 Instead Of 0?
I don't understand why this count will return 1, when it should return 0. If I enter the resource_id 1, it will return 2, which is correct. But if I enter any other resource_id, all of which do not exist in the 3 rows of data I have, it returns 1....
MySQL COUNT & Return
I'm TRYING to pull every member from a database and count how many articles they have written from one SQL Query. "SELECT `tbl_user`.*, COUNT(`tbl_product`.`pd_author`) AS `num_articles` FROM `tbl_user`, `tbl_product` WHERE `tbl_user`.`user_statues` = 'active' AND `tbl_user`.`user_name` = `tbl_product`.`pd_author` GROUP BY `tbl_user`.`user_name` ORDER BY `tbl_user`.`user_id`" Tables: tbl_user & tbl_product The Problem: It works fine except for people with no articles but i still would like it to display there details from tbl_user.
SELECT Count(xxx) AS Xxx Does Not Return Integer
i'm using the latest downloaded version of MySQL and am currently producing an app that is compatible with MySQL, MS Access and SQL 2000/2005, i've not really come across any problems making the app run across the 3 platforms (except for Access's wierd date syntax and MySQL's lack of a boolean field) but the followin is doing my head in: If i run a count query such as SELECT Count(CategoryID) AS CountCats FROM MyDownloads etc etc CountCat = (Downloads.Fields.Item("CountCats").Value) And then run an IF statement in my page code such as: <% If CountCat = 0 Then .... %> This works fine in Access and SQL as the Count value is seen as an integer, but in MySQL this chucks an error unless the code reads <% If CountCat = "0" Then .... %> Why is the count value not returned as an integer????? I cant understand that at all, what else could it be? I've seen another post on the forum along the same lines but with no answer to it. This is grim as every page that uses a count needs to have an additional IF statement put on it to change the second IF statement if the database is detected as MySQL.
Return A Count Of Number Of Rows Before Desired Row.
Anyone know how? find_in_set doesn't seem to be able to do what I want since I am not searching through an array but am actually running a query. What I really want is to be able to select all until something = something. I've searched the realm of google but cannot come up with anything useful, and so I am wondering if maybe I don't know the right words to be looking for.
Agregate Count Return All Rows, Left Join
I have 2 tables on a lyric discussion site Titles title_id title_name title_lyricist title_lyrics title_artist title_entered And POSTS post_id title_id post_author post_text I want to be able to list all the titles, and count the # of posts for each title. The problem is some titles have zero posts so my query ignores thos titles, butI syill want them returnd where Ill add a ')' for count. This is the best i could come up with, but still only those that have posts are returned. I still want the titles that don't have a match in the posts table to be returned with a total_count of zero.
Default MySQL Return Value For Query
I'm writing a multilingual web site where not all content will necessarily be multilingual. I have a languageid and want to retrieve the content for that language. However, if that content doesn't exist, I would want to retrieve content for the default language. My ideal query would be something like: SELECT content FROM page_content WHERE pageid=$pid AND languageid=$userlang IF NO RESULTS WHERE pageid=$pid AND languageid=$defaultlangid I imagine my made- up 'IF NO RESULTS' directive does not exist. Is there any way to get that kind of functionality from one query? Since I will be having queries all over the site to retrieve different kinds of localized things (image tags, html code, textual content), it would be awesome if I could figure out a way to consolidate. Maybe there is a way to sort results from this query: SELECT content FROM page_content where pageid=$pid AND (languageid=$userlang OR languageid=$defaultlang) ORDER BY .... ? I'm also not sure if the nested boolean will work.
Need Query That Will Return Firstnames With X Characters
I have spammers who post on my website. They like to use things like Home-loan-refinancing as their first name when registering. So I need a query that will return all rows where the firstname field contains 20+ characters or something like that.
The Query Cache Does Not Return Stale
I am reading up on query cache feature and would like to have the following question clarified. From mysql 5.0 manual, section 5.14, it says "The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed." I would like to know what is the definition of "when tables are modified". does it mean? 1. new records inserted into table? 2. records gets updated? 3. table structure is modified by using alter table command?
Lost Connection To MySQL Server During Query Socket Error On Read. WSAGetLastError Return 10054($2746)
We just migrated our MySQL database from a NetWare server running version 5.0.21 to a openSUSE box running version 5.0.45. Everything ran fine for a couple of days, then clients began losing their connections and creating new ones without the old threads being terminated. This behavior is the same for the Delphi client we use to access our database as well as the MySQL Administrator tool. I've tried adjusting connection_timeout, but overall the settings for this server are the same as the previous one that ran without issues for a year.
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?
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
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.
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?
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.
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....
Count Records Within Query
I have the following information in table 'Test1': field1 field 2 000001 000000 000002 000001 000003 000000 000004 000000 I want to write a query that will only display the unique field 2 records and as well provide a count of how many records have that id. output should look like field2 recno 000000 3 000001 1 can some one help with the trick to make this work?
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
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?
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
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?
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.
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?
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:
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:
Return Only 3 Photos From 3 Specified Galleries In One Query (was "Is It Possible?")
Let me start from the beginning.... I have a table that lists photo details, with a foreign key to link them to a gallery. Lets say I have 20 photos altogether and 4 galleries, (for simplicity sake, 5 photos per gallery). My question is: Is it possible to query the table to get it to return only 3 photos from 3 specified galleries in one query? (So returning 9 photos altogether, 3 from each gallery) Rather than querying the table 3 times with a limit of 3 and merging the results.
Query Table A And Return Records Not Found In Table B.
Given table A and table B. Table A is a master table and table B is a list of authority or reference which is fed by selecting records from table A (via an UI I wrote in PHP). The interface is working fine but I am left with one problem. If I need to edit my table of authority, the source table (table A) should return ONLY entries not previously selected. That said, if the A.key is in B.key, records from table A matching B.key should be omitted. I could do this by looping through the query results from table A and querying table B, if no match, show A.key. This I think is a bit antiquated and possibly too involved. Is there a joint query command I can use?
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']}'
Preventing A Count Query Inside A Loop How?
I have an idea in my head and im pretty sure its possible but I don't have enough mysql knowledge to apply. Basically, I have this: SELECT user_id FROM ml_group_subscriptions WHERE user_group = $id then in php I initialise a loop go over the results: PHP while ($row = mysql_fetch_array($get_users)) { $this_user = $row['user_id']; and then an insert query: INSERT INTO ml_subscriptions (`user_id`, `campaign_id`) VALUES ($this_user, $add_to_campaign) while this is all dandy, I don't want duplicate data. My only current method would be PHP $get_users = mysql_query("SELECT user_id FROM ml_group_subscriptions WHERE user_group = $id"); while ($row = mysql_fetch_array($get_users)) { $this_user = $row['user_id']; $check = mysql_result(mysql_query("SELECT COUNT(user_id) FROM ml_subscriptions WHERE user_id = $this_user"),0); if ($check == 0) { $insert_users = mysql_query("INSERT INTO ml_subscriptions (`user_id`, `campaign_id`) VALUES ($this_user, $add_to_campaign)"); } } as you can see this is putting a query inside a loop which could be potentially huge. Can I not adapt the first SELECT query to only accept data that is not about to be duplicated?
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?
Trying To Count The Number Of Rows In A Result Set After Query
The user fills out this form to sign up to the website, the form checks the database to see if the username has already been taken with the code: $conn = mysql_connect("localhost:3306", "root", "********") or die ("Error With Connection"); echo("connected<br><br>"); $db_sel = mysql_select_db("game",$conn) or die ("Error With Database"); $check = "select * from users where 'username' = '$username'"; $db_sel = mysql_query($check,$conn) or die (mysql_error());
How To Improve The Speed Of Mysql Query Using Count(*)
I'm using this kind of queries in mysql in InnoDB engine Select count(*) from marking1 where persondate between '2007-04-23 00:00:00.000' and '2007-04-23 23:59:59.999' and PersonName='aaa' While executing these queries from front end VB, It takes above 5 secs with 50 thousand records. How can I improve speed for this kind of queries. Is there any alternation for this command.
Query The Article Table And Get A Count Of All Related Comments
Could someone please help me with this query. I have two tables -- one for my articles and another for my comments. Comments are stored in the comments table with a corresponding article id. I want to query the article table and get a count of all related comments. The following query doesn't seem to work for me. Can you please suggest how I fix it? SELECT a.id AS id, a.title AS title, a.preview AS preview, a.thumbnail AS thumbnail, a.category AS category, a.timestamp AS timestamp, a.game AS game, count( b.id ) AS commentcount FROM article_table AS a, comment_table AS b LEFT JOIN comment_table ON a.id = b.article WHERE a.news = 'Yes' AND a.type = Ƈ' AND a.saved != Ƈ' GROUP BY b.article ORDER BY a.timestamp DESC LIMIT 5
Count One Table's Rows From Multi Table Query
here are my tables (condensed) FEEDS feed_id site_id SITE site_id site_name ARTICLES article_id feed_id link I want to create a query that returns the total number of articles for every site_id (which is unique in the SITE table). I have this: PHP $gsite = mysql_query("SELECT site.site_id, feeds.feed_id, COUNT(articles.article_id) AS acont FROM site,feeds,articles WHERE feeds.site_id = site.site_id AND articles.feed_id = feeds.feed_id group by site.site_id", $connection) or die(mysql_error()); The query does not use JOIN, ON and all that good stuff. I just need the following variables to run through a loop: site_id the number of articles rows per site_id
Extending Simple Query With Specific COUNT() From Second Table
Currently i basically do following: ----------------------------------- SELECT cat_id, cat_name FROM categorytable WHERE blah=something ORDER BY pos ASC while(FetchRowAssoc()) { SELECT COUNT(*) as itemcount FROM itemtable WHERE category=cat_id echo cat_it, cat_name, itemcount, blah... etc. } ------------------------------------ I fetch some info from the category table and then I want to know how many items I have in the 2nd table which are associated to the categories in question from the first table. Can this be done with a single query statement, so i get from FetchRowAssoc the cat_id, the cat_name and the count of the items for this category? "SELECT t1.cat_id, t1.cat_name, COUNT(t2) as itemcount FROM categorytable as t1, itemtable as t2 WHERE t1.blah=something ORDER BY t1.pos ASC " <- like this but working, i really don't get how to properly write the syntax there... also didn't found a useful example with googling.
Sub Query And Count Query
i have a database with the following structure id | MoveDate | ItemId | SiteID (a new entry is entered when an item is moved from 1 site to another) and i am trying to forumlate a query so that i can count how many days each item was at a specific location so lets assumes i have the following data 1 | 01/01/2007| 1 | 1 2 | 03/01/2007| 1 | 2 how can i run a query that will tell me that between the dates 01/01/2007 and 08/01/2007 item 1 was at site 1 for 2 days and site 2 for 5
Count Query Causing "excessive Processor Usage"
For the second time in the past 6 months my hosts have shut down my site because it was apparently causing a shared mySQL 5 server to max out at 100% usage. I'm not much a programmer, know even less about mySQL and obviously don't have access to the server from the back end but I'd appreciate any advice as to whether it is my query that is causing the problem: Set rsMail = objConnC.Execute("SELECT * ,(SELECT COUNT(*) FROM comments WHERE comments.submissionID = submissions.submissionID AND comments.commentInclude = 1) as CommentCount FROM submissions WHERE PigeonHole = 'mailbag' AND Status <> 'hold' ORDER BY submissionID DESC LIMIT 20") Apart from two instances, this query has been working fine on a page that receives between 10,000 and 30,000 visits a day. The site is running on Windows and coded in ASP but is calling a separate mySQL server.
Count User Comments In Each Category (was "help On A Query")
I would really appreciate if someone could help me out on this one. this are my tables: comments Comment_ID Video_ID Comment_Text Comment_UserID .... video Video_ID Category_ID ..... category Category_ID Category_Name I'm trying to get a user's count of comments for each category, ordered by the number of comments DESC. basically, I want to show the top categories where the user has posted the highest number of comments. so far this query gets me the category name of where the user has posted: PHP $SQLTemp = "SELECT Category_Name FROM category WHERE Category_ID IN ( SELECT DISTINCT ( Category_ID ) FROM video WHERE Video_ID IN ( SELECT DISTINCT ( Video_ID ) FROM comments WHERE Comment_UserID = $id ) )"; THANKS!!
Count Of *unique* Visits By Page For Each Day (was "Need Help With Query")
Using MySQL 4.1 I have a table 'orders' with the following fields: ipaddress pagenum xtime (which is just the yearmonthday) this is the query: SELECT distinct pagenum,ipaddress,xtime,count(pagenum) AS pagenumtotal FROM `orders` group by pagenum,xtime ORDER by xtime, pagenum asc What I am trying to do is return a count of *unique* visits by page for each day. This query returns a count of *all* visits by page for each day, including repeats. So if a visitors hits a certain page on the site 10 times and adds a record 10 times, the count result will include all 10 records in the count. I want this to only count as 1.
#1136 - Column Count Doesn't Match Value Count At Row 1
I'm getting the above error with the following SQL Statement. I cannot seem to find the error in the code. The select statement does pull multiple rows. I'm using my SQL version: 4.1.19 Any clue on why this isn't working? INSERT INTO `Grants` ( `Project_Code` , `Grant_Code` , `Fiscal_Year` , `Capital` ) VALUES ( ( SELECT Project_Code, Grant_Code, FY, SUM( Capital ) FROM Grants_Temp WHERE Project_Code = 'OSUT' AND FY = '2006' AND Claim_Month = '072006' GROUP BY Grant_Code ORDER BY Grant_Code ) )
Add COUNT(*) To Already Existing Query (was "How To Do This")
I have this query: PHP SELECT entries.*, categories.*, uploads.*, entries.blog_id FROM entriesINNER JOIN categories ON (categories.category_id = entries.category_id) LEFT JOIN uploads ON(uploads.blog_id = entries.blog_id) LEFT JOIN images ON (images.blog_id = entries.blog_id) ORDER BY entries.date_submitted DESC LIMIT 3 Now i want to add this PHP SELECT COUNT(*) FROM comments but i dont know how to add it into the already existing query. The messages table has a column blog_id column in it so i could do a join like thisd LEFT JOIN comments ON PHP (comments.blog_id = entries.blog_id) but i dont know how to get the first bit in there
|
TRACKER
