List:General Discussion« Previous MessageNext Message »
From:<webmaster Date:May 13 2010 2:18pm
Subject:RE: Count Query question
View as plain text  
Hi Keith,

The way I would go about this is to try and pinpoint what the issue is, by breaking the
query up.

For instance, if you remove the 'product_quantity > 0' condition, do you get any more
rows in your result ?

I also notice that your 'products_date_avaiable' is defaulting to '2008-10-01 00:00:00'
which is the same date in your two sample rows.

Run a query like
  SELECT distinct (products_date_available) FROM products

and see if there are there are any other dates ... if there are only 2, then you'll get
only two rows.

Thanks,
Justin Tifang

-----Original Message-----
From: Keith Clark [mailto:keithclark@stripped] 
Sent: 13 May 2010 14:11
To: mysql@stripped
Subject: Re: Count Query question

Bob,

Here are a few rows of my data:

17462, 0, '0929998596', '/GraphicNovels/0929998596.jpg', '8.5000',
'2010-05-12 19:02:47', '2008-10-01 00:00:00', '2008-10-01 00:00:00',
'0.50', 1, 1, 7429, 0, '1', 

17461, 1, '1561481912', '/Cooking/1561481912.jpg', '3.0000', '2010-05-12
19:00:17', '2008-10-01 00:00:00', '2008-10-01 00:00:00', '0.50', 1, 1,
7428, 0, '1', 

Here is the query I ran:

SELECT date(products_date_available) as Date,
COUNT(products_quantity) as 'Titles Available',
SUM(products_quantity) as 'Books Available'
FROM products
WHERE products_quantity > 0
GROUP BY date(products_date_available);

And I only got back two rows from over 2 years of daily entries:

'2008-01-01', 3327, '3736'
'2008-10-01', 2739, '2904'

I'm not sure I understand where I'm going wrong.

Keith


On Wed, 2010-05-12 at 21:54 -0500, Bob Cole wrote:
> Kevin:
> I assumed the following data:
> products_id	products_date_available	products_quantity
> 11	2010-05-01	1
> 11	2010-05-02	0
> 11	2010-05-03	3
> 11	2010-05-04	3
> 11	2010-05-05	3
> 11	2010-05-06	1
> 11	2010-05-07	0
> 11	2010-05-08	3
> 11	2010-05-09	3
> 11	2010-05-10	3
> 11	2010-05-11	3
> 11	2010-05-12	3
> 22	2010-05-01	1
> 22	2010-05-02	2
> 22	2010-05-03	0
> 22	2010-05-04	3
> 22	2010-05-05	3
> 22	2010-05-06	1
> 22	2010-05-07	0
> 22	2010-05-08	3
> 22	2010-05-09	0
> 22	2010-05-10	3
> 22	2010-05-11	3
> 22	2010-05-12	3
> 33	2010-05-01	1
> 33	2010-05-02	2
> 33	2010-05-03	3
> 33	2010-05-04	3
> 33	2010-05-05	3
> 33	2010-05-06	0
> 33	2010-05-07	0
> 33	2010-05-08	3
> 33	2010-05-09	3
> 33	2010-05-10	0
> 33	2010-05-11	3
> 33	2010-05-12	3
> 
> and used the following query:
>      SELECT products_date_available, COUNT(products_quantity),
> SUM(products_quantity)
>      FROM products
>      WHERE products_quantity > 0
>      GROUP BY products_date_available
> 
> and got the following results:
>      products_date_available   COUNT   SUM
>      2010-05-01 00:00:00          3               3
>      2010-05-02 00:00:00          2               4
>      2010-05-03 00:00:00          2               6
>      2010-05-04 00:00:00          3               9
>      2010-05-05 00:00:00          3               9
>      2010-05-06 00:00:00          2               2
>      2010-05-08 00:00:00          3               9
>      2010-05-09 00:00:00          2               6
>      2010-05-10 00:00:00          2               6
>      2010-05-11 00:00:00          3               9
>      2010-05-12 00:00:00          3               9
> 
> One line for each day except that 2010-05-07 is missing because each product had 0
> quantity on that day.
> For example, on 2010-05-01, there were 3 products (each with a quantity of 1) for a
> total quantity of 3.
> I wonder if I am representing your situation correctly.  What am I missing?
> Bob
> 
> On May 12, 2010, at 8:00 PM, Keith Clark wrote:
> > Hi Bob,
> > No, actually it does not.  I'm looking for the count of items.  From
> > your query example I only get two rows.  This table has over 2 1/2 years
> > of daily sales data.
> > Maybe I'm not stating my question correctly...hmmmm....
> > Thanks for responding though, greatly appreciated.
> > Keith
> > On Wed, 2010-05-12 at 19:46 -0500, Bob Cole wrote:
> >> Keith:
> >> Does this work?
> >>     SELECT products_date_available, COUNT(products_quantity)
> >>     FROM products
> >>     WHERE products_quantity > 0
> >>    GROUP BY products_date_available
> >> Hope this helps,
> >> Bob
> >> On May 12, 2010, at 3:06 PM, Keith Clark wrote:
> >>> On Wed, 2010-05-12 at 10:13 -0400, Keith Clark wrote:
> >>>> Chris,
> >>>> Here is my full table definition:
> >>>> 
> >>>> CREATE TABLE `products` (
> >>>> `products_id` int(15) NOT NULL AUTO_INCREMENT,
> >>>> `products_quantity` int(4) NOT NULL,
> >>>> `products_model` varchar(15) NOT NULL DEFAULT '',
> >>>> `products_image` varchar(64) DEFAULT NULL,
> >>>> `products_price` decimal(15,4) DEFAULT NULL,
> >>>> `products_date_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
> >>>> `products_last_modified` datetime DEFAULT '2008-10-01 00:00:00',
> >>>> `products_date_available` datetime DEFAULT '2008-10-01 00:00:00',
> >>>> `products_weight` decimal(5,2) DEFAULT '0.50',
> >>>> `products_status` tinyint(1) NOT NULL DEFAULT '1',
> >>>> `products_tax_class_id` int(11) DEFAULT '1',
> >>>> `manufacturers_id` int(11) DEFAULT NULL,
> >>>> `products_ordered` int(11) DEFAULT '0',
> >>>> `products_format` varchar(20) DEFAULT NULL,
> >>>> `abebooks_price` decimal(15,4) DEFAULT NULL,
> >>>> PRIMARY KEY (`products_id`,`products_model`),
> >>>> UNIQUE KEY `products_model` (`products_model`),
> >>>> KEY `idx_products_date_added` (`products_date_added`),
> >>>> KEY `manufacturers_id` (`manufacturers_id`)
> >>>> ) ENGINE=MyISAM AUTO_INCREMENT=17418 DEFAULT CHARSET=latin1
> >>>> 
> >>>> So, I'd like to create a report that grouped by
> products_date_available,
> >>>> counts all records before products_date_available with a
> >>>> products_quantity>0.
> >>>> 
> >>>> 
> >>> I don't think I'm asking this question properly.
> >>> 
> >>> For every date in products_date_available in the table, I'd like to
> know
> >>> the count of items available with products_quantity>0 up until that
> >>> date.
> >>> 
> >>> So if there are 500 days in the table, there should be 500 rows in the
> >>> report.  Each showing the products available as of that date in time.
> >>> 
> >>> I hope that clarifies it.  I can write a query to do so for each
> >>> individual date, just not a report for all dates at the same time.
> 
> 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1


__________ Information from ESET NOD32 Antivirus, version of virus signature database 5112
(20100513) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com


 

__________ Information from ESET NOD32 Antivirus, version of virus signature database 5112
(20100513) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
 

Thread
Count Query questionKeith Clark12 May
  • Re: Count Query questionChris W12 May
    • Re: Count Query questionKeith Clark12 May
      • Re: Count Query questionKeith Clark12 May
        • Re: Count Query questionBob Cole13 May
          • Re: Count Query questionKeith Clark13 May
            • Re: Count Query questionBob Cole13 May
              • Re: Count Query questionKeith Clark13 May
                • RE: Count Query questionwebmaster13 May