CODEWITHSUNDEEP
androidsqlitejoincount
Gabriel Matusevich
Gabriel Matusevich

Reputation: 3855

SQLite COUNT JOIN DISTINCT

I'm sure this is a duplicate but I cannot find the answer:

SQLITE query issue

Tables:

Relation: Client has Many Order

Success condition:

Obtain the number of clients with orders

Query: 

SELECT COUNT(Client.id) AS count
  FROM Client
  INNER JOIN Order
    ON Order.id = Client.id
    AND Order.storeId = Client.storeId // This is because Clients have many stores
  WHERE
    AND Order.id IS NOT NULL
    AND Order.date BETWEEN '12-09-2016 16:00:00' AND '13-09-2016 16:00:00'

Problem

If Client 1, Store 1 has 3 Orders the query should return 1 because is the same client and the same store BUT is returning 3 meaning is not counting the clients with Orders but the orders themselves

Solution? Keep in mind this is SQLite and is being used in Android 4.1.1 so some SQLite functions may not be available

Upvotes: 1

Views: 590

Answers (1)

juergen d
juergen d

Reputation: 204924

The inner query gets all the clients with orders. The outer query counts those records

select count(*)
from
( 
      SELECT Client.id
      FROM Client
      INNER JOIN Order ON Order.id = Client.id
                      AND Order.storeId = Client.storeId
      WHERE Order.date BETWEEN '12-09-2016 16:00:00' AND '13-09-2016 16:00:00'
      GROUP BY Client.id
) tmp

a simpler version would be to count the number of different (distinct) clients

  SELECT count(distinct Client.id)
  FROM Client
  INNER JOIN Order ON Order.id = Client.id
                  AND Order.storeId = Client.storeId
  WHERE Order.date BETWEEN '12-09-2016 16:00:00' AND '13-09-2016 16:00:00'

Upvotes: 2

Related Questions