Gabriel Matusevich Gabriel Matusevich - 2 months ago 12
Android Question

SQLite COUNT JOIN DISTINCT

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

SQLITE query issue

Tables:


  • Client

  • Order



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

Answer

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'
Comments