Help with another mysql query

I Would like to select the count of the newly added DISTINCT product_id in the table

ID    product_id
 1      abc
 2      abc
开发者_运维知识库 3      cat
 4      dog
 5      dog
 6      man
 7      man
 8      bat
 9      bat
10      abc
11      cat
12      dog
13      dog

I want a query that gives me a similar result from the above table -

dog -2

cat -1

abc -1

bat -2

man -2

*Thanks in advance

*EDIT

I have a table to store images of products, I keep adding new images of products in this table and I want to select the count of the newly added images of that particular product(ignoring the previous sets of images added earlier in the table) ... I hope I have made myself clear

*EDIT I found the answer to my question..

SELECT COUNT(xbrygame_scrnsmkv.id) FROM xbrygame_scrnsmkv 
WHERE xbrygame_scrnsmkv.id >(SELECT  MAX(xbrygame_scrnsmkv.id) 
                             FROM xbrygame_scrnsmkv 
                             WHERE xbrygame_scrnsmkv.product_id= (SELECT  DISTINCT xbrygame_scrnsmkv.product_id 
                                                             FROM xbrygame_scrnsmkv 
                                                             ORDER BY xbrygame_scrnsmkv.id DESC LIMIT 5,1)) 
   GROUP BY xbrygame_scrnsmkv.product_id

You could redesign your table to also have a counter:

ID    product_id  consecutive
 8      bat         1
 9      bat         2
10      abc         1
11      cat         1
12      dog         1
13      dog         2

When inserting to the table, first check the last entry and its consecutive counter. If you are inserting the same element, increase the counter, otherwise insert 1. Something like:

INSERT INTO Table (product_id, consecutive) VALUES
('newProd', CASE (SELECT product_id FROM Table WHERE ID = MAX(ID))
              WHEN 'newProd' THEN
                           (SELECT consecutive FROM Table WHERE ID = MAX(ID)) + 1
              ELSE 1
            END);

Then you can make your selection as:

SELECT product_id, consecutive
FROM Table
WHERE ID IN
  (SELECT MAX(ID)
   FROM Table
   GROUP BY product_id)

Original answer was:

SELECT Filename, COUNT(Filename) as Count
FROM Table
GROUP BY Filename

If you only want 'newly added' records, you will have to indicate somehow what a newly added record is. Your dbms may support dating by default on all records (I have an inkling that Ingres may do this, but I may be imagining it - it's been a while since I've used it), but you will probably need to add something to the row yourself.

You could add a datetime field to filter on, then you can do as Zed has done:

SELECT Filename, COUNT(Filename) as Count
FROM Table
WHERE DateAdded > (FilterDate)
GROUP BY Filename

(EDIT where FilterDate is the DateTime after which you deem a record to be new, for example you could set this to Now minus 60 minutes - the syntax will change depending on DBMS)

or you could add a 'new' flag bit column and set it to true for each new record, them turn it off after it is read or some arbitary interval.

SELECT COUNT(xbrygame_scrnsmkv.id) FROM xbrygame_scrnsmkv 
WHERE xbrygame_scrnsmkv.id >(SELECT  MAX(xbrygame_scrnsmkv.id) 
                             FROM xbrygame_scrnsmkv 
                             WHERE xbrygame_scrnsmkv.product_id= (SELECT  DISTINCT xbrygame_scrnsmkv.product_id 
                                                             FROM xbrygame_scrnsmkv 
                                                             ORDER BY xbrygame_scrnsmkv.id DESC LIMIT 5,1)) 
   GROUP BY xbrygame_scrnsmkv.product_id