help with sql query

[belfasttim]

Hi--

I'm doing a query to pull some records for a report on product sales. Basically, the table looks like this--

Store ID ¦ Product ID
------------------------
12 ¦ 45
11 ¦ 45
10 ¦ 45
12 ¦ 45
19 ¦ 45
01 ¦ 45
01 ¦ 45
01 ¦ 45

Now, the interesting thing is that I want to pull this report for each store-- and I don't want the individual store to know what the other stores have been selling, except in the case of the headquarters (01).

I can get most of the way there with something like this--

SELECT store_id, COUNT(*) FROM `sales` WHERE product_id = '45' GROUP BY store_id

This gives me a result like

Store ID ¦ Product ID
------------------------
01 ¦ 3
10 ¦ 1
11 ¦ 1
12 ¦ 2
19 ¦ 1

But can I alter my query to produce something more like this (in the case of a report for store 12)--

Store ID ¦ Product ID
------------------------
01 ¦ 3 // this is ok to show, since 01 is HQ
12 ¦ 2
** ¦ 3 // everyone else

Thanks for any ideas!

[ZydoSEO]

Why not only show the total for the store pulling the report? Saying 3 other (**) were sold really tells the user nothing useful that I see. They can't really get a good comparison since they don't know if 1 store sold all 3 or if 3 of 10 sold one each while the other 7 sold none.

[belfasttim]

Hi Zydo-- I think I see what you mean, but that's not really the point.

Because of the way the stores compete with each other (consider them independent agents or franchises if you like) the HQ store (01) doesn't want them to know who is selling what. Therefore I need to make sure that the store pulling the report only sees its own sales, the HQ sales, and then "everyone else"-- but not how many each particular other franchise is selling.

Thanks