Skip to content

Instantly share code, notes, and snippets.

@opotemkin
Created June 9, 2017 12:45
Show Gist options
  • Save opotemkin/5e78348883fe5ad5e50f6d99f73ed68d to your computer and use it in GitHub Desktop.
Save opotemkin/5e78348883fe5ad5e50f6d99f73ed68d to your computer and use it in GitHub Desktop.
Несколько COUNT в одном SQL-запросе
Например, необходимо из таблицы заказов вытащить кол-во доступных текущих заказов, и кол-во заказов, доступных через час.
SELECT "queue".*, COUNT(DISTINCT "order1".id) as count, COUNT(DISTINCT "order2".id) as count_hour
FROM "queue"
LEFT JOIN "order" as "order1" ON "queue".id="order1".queue_id
LEFT JOIN "order" as "order2" ON "queue".id="order2".queue_id
WHERE (
("order1"."updated_at"<=:current_time AND "order1"."updated_at" IS NOT NULL)
OR "order1"."updated_at" IS NULL)
AND (
("order2"."updated_at"<=:current_time_hour AND "order2"."updated_at" IS NOT NULL)
OR "order2"."updated_at" IS NULL)
AND "queue".active=:active
GROUP BY "queue".id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment