I have this table and I’m trying to determine how many Tables (in a restaurant) are awaiting food. The table( in the database) has the following columns: TableNumber, ObjectType, ObjectDescription and Quantity (amongst others).
I made this query to see what is ordered, but would like to know how many unique tables are waiting for food…..
SELECT TableNumber AS TAFEL
, COUNT (*) AS AANTAL
, ObjectDescription AS PRODUCT
FROM TableMemory
WHERE (ObjectType = 1 OR ObjectType = 17)
GROUP BY TableNumber, ObjectType, ObjectDescription
ORDER BY TableNumber
which gives me the following output…
TAFEL AANTAL PRODUCT ### OF TABLES ----------- ----------- -------------------------------------------------- 1 1 Black Jacks Hotdog 5 1 5 Friet Groot 1 2 Friet Klein 1 1 Friet Middel 1 1 Knoflooksaus 1 1 Uitsmijter Ham/kaas 1 1 Uitsmijter Lou 3 1 Knoflooksaus 3 1 New York Hotdog 7 1 Broodje Gezond 7 1 Knoflooksaus 40 1 Friet Groot 40 1 Met Uitjes 60 1 Friet Middel 60 1 Meenemen
I tried to use count and distinct in the query but nothing I tried seems to give me an extra output for the unique number of tables(restaurant) which should be 5 in the above result.
Can anyone help me? Grateful in advance!
Advertisement
Answer
To add a column which contains the (repeated) [### OF TABLES] you could use CROSS JOIN
/* This will repeat the [### OF TABLES] for each row of the results */
;with
prod_cte as (
SELECT TableNumber AS TAFEL
, COUNT (*) AS AANTAL
, ObjectDescription AS PRODUCT
FROM TableMemory
WHERE (ObjectType = 1 OR ObjectType = 17)
GROUP BY TableNumber, ObjectType, ObjectDescription),
total_cte as (
SELECT COUNT(DISTINCT TableNumber) [### OF TABLES]
FROM TableMemory
WHERE ObjectType IN (1, 17))
select p.*, t.*
from prod_cte p
cross join total_cte t;
[Edit] Without the CTE to work in SQL 2000
select p.*, t.*
from (
SELECT TableNumber AS TAFEL
, COUNT (*) AS AANTAL
, ObjectDescription AS PRODUCT
FROM TableMemory
WHERE (ObjectType = 1 OR ObjectType = 17)
GROUP BY TableNumber, ObjectType, ObjectDescription) p,
(
SELECT COUNT(DISTINCT TableNumber) [### OF TABLES]
FROM TableMemory
WHERE ObjectType IN (1, 17)) t;