CODEWITHSUNDEEP
mysqlsqlcountdistinct
user2755209
user2755209

Reputation: 85

Count in sequential order

I have a data set that has a list of id's and with those id's are a list of categories (so each id may have many categories). I want to search a list of categories and see how many different id's are within that category (count) but once a id is counted in one category it will not be counted in another.

Example:

ID  Category
1    Gas Station
1    Convenience Store
1    Barber
2    Day Care
2    Gas station
3    Convenience Store
3    Golf Range

So if I am doing a search of counts on gas station and convenience store (in that order) Gas Station will get a count of 2 (For id 1&2) and then Convenience store will get a count of 1 (id 3).

Currently my query looks like:

select category,distinct(id) from TABLE
where id in ('Gas Station','Convenience Store')
group by category

and it will give me 

Gas Station - 2
Convenience Store - 2

And it's not what I want. Desired output:

Gas Station - 2
Convenience Store - 1

Upvotes: 0

Views: 201

Answers (2)

gwc
gwc

Reputation: 1293

Updated

Try this as a single SQL:

SELECT Category
 ,COUNT(*)
 ,@ids:=CONCAT( @ids, ID, ',' )
 FROM Table1, (SELECT @ids:=',') ids
 WHERE Category IN ('Gas Station','Convenience Store')
 AND POSITION( CONCAT( ',', ID, ',' ) IN @ids ) = 0
 GROUP BY Category

SQLfiddle at http://sqlfiddle.com/#!2/2f026/12

Updated

Ok. try this:

SET @ids:=',';
SELECT Category
      ,COUNT(*)
      ,@ids:=CONCAT( @ids, ID, ',' )
  FROM Table1
 WHERE Category IN ('Gas Station','Convenience Store')
   AND POSITION( CONCAT( ',', ID, ',' ) IN @ids ) = 0
 GROUP BY Category

Modifying @peterm's sqlfiddle (http://sqlfiddle.com/#!2/2f026/1) the results are:

CATEGORY             COUNT(*)   @IDS:=CONCAT( @IDS, ID, ',' )
Convenience Store    1          ,1,3,
Gas Station          2          ,1,

Upvotes: 1

peterm
peterm

Reputation: 92805

It's not very clear why you want this output but technically you can produce it with a query

SELECT category, COUNT(DISTINCT id) count
  FROM table1
 WHERE category = 'Gas Station'
UNION ALL
SELECT category, COUNT(DISTINCT id) count
  FROM table1
 WHERE category = 'Convenience Store' 
   AND id NOT IN
       (
         SELECT DISTINCT id
           FROM table1
          WHERE category = 'Gas Station'
       );

Output:

|          CATEGORY | COUNT |
|-------------------|-------|
|       Gas Station |     2 |
| Convenience Store |     1 |

Here is SQLFiddle demo

Upvotes: 1

Related Questions