SQL Query to determine number of tables

Question

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!

Answer 1

Firstly its very important to tell us if you are using a vintage version of SQL Server. A lot has changed since 2000!

Secondly, really you are going about obtaining your data the wrong way. You are trying to combine 2 sets of data which aren't related. The correct way to handle this is to pull 2 datasets, 1 with the number of tables (Gordon has already provided this), and one with the aggregated values per table (which you already have). If for some reason you really can't do this then see the option below using a simple sub-query.

SQL Server - Window Functions Not Supported

Use a simple sub-query, being careful to duplicate the where clauses.

SELECT TableNumber AS TAFEL
    , COUNT (*) AS AANTAL
    , ObjectDescription AS PRODUCT
    , (
      SELECT COUNT(DISTINCT TableNumber) 
      FROM TableMemory 
      WHERE ObjectType IN (1, 17)
    ) TableCount
FROM TableMemory 
WHERE ObjectType IN (1, 17)
GROUP BY TableNumber, ObjectType, ObjectDescription
ORDER BY TableNumber;

SQL Server - Window Functions Supported

With reference to Gordon's existing answer to a similar question (which I admit could be hard to translate, hence why I haven't flagged as a duplicate), you can do it as follows.

1. Compute a row number partitioned by TableNumber.
2. Count how many rows with row number = 1 there are. Because row number 1 will only exist once per TableNumber. Note I do this in a CROSS APPLY to avoid duplicating the logic twice, once in the SELECT and once in the GROUP BY.

WITH cte AS (
  SELECT TableNumber AS TAFEL
      , ObjectDescription AS PRODUCT
      , ROW_NUMBER() OVER (PARTITION BY TableNumber ORDER BY ObjectDescription) rn
  FROM TableMemory T
  WHERE ObjectType IN (1, 17)
)
SELECT TAFEL, PRODUCT
    , COUNT (*) AS AANTAL
    , SUM(RowToCount) OVER () TableCount
FROM cte
CROSS APPLY (VALUES (CASE WHEN rn = 1 THEN 1 ELSE 0 END)) AS X (RowToCount)
GROUP BY TAFEL, PRODUCT, RowToCount
ORDER BY TAFEL;

Answer 2

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;

Answer 3

Assuming that "tafel" is table, then just use count(distinct):

SELECT COUNT(DISTINCT TableNumber) 
FROM TableMemory 
WHERE ObjectType IN (1, 17);

If you want your original data with the number of distinct tables, then just add dense_rank() twice:

SELECT TableNumber AS TAFEL, COUNT (*) AS AANTAL,
       ObjectDescription AS PRODUCT,
       (-1 +
        DENSE_RANK() OVER (ORDER BY TableNumber ASC) +
        DENSE_RANK() OVER (ORDER BY TableNumber DESC)
       ) as num_tables
FROM TableMemory 
WHERE (ObjectType = 1 OR ObjectType = 17)
GROUP BY TableNumber, ObjectType, ObjectDescription
ORDER BY TableNumber;