count and group by question

From: <ryan(at)paymentalliance(dot)net>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: count and group by question
Date: 2002-06-19 19:19:25
Message-ID: Pine.LNX.4.33.0206191430010.11690-100000@paymentalliance.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I have a query which contains both a group by and a count, e.g:

SELECT
to_char(pa_shopping_cart.delivery_date, 'FMMM/FMDD/YY') AS delivery_date,
pa_products.product_name AS product_name,
pa_orders.order_state AS state,
count(*) AS count
FROM
pa_shopping_cart,
pa_products,
pa_orders
WHERE
pa_shopping_cart.order_id = pa_orders.order_id AND
pa_shopping_cart.product_id = pa_products.product_id
GROUP BY
pa_shopping_cart.delivery_date,
pa_products.product_name,
pa_orders.order_state
ORDER BY
pa_shopping_cart.delivery_date, pa_products.product_name;

This query is really handy because it gives me the count of each
product grouping by delivery within each possible order state.

Here's the question - I would like to get the count of how many tuples are
returned total. With most queries, count(*) works great for this purpose,
however I need something that will give me the total count of tuples
returned even when there is a grouping.

Any ideas?

Ryan Mahoney

Browse pgsql-hackers by date

  From Date Subject
Next Message Hannu Krosing 2002-06-19 20:07:11 Re: count and group by question
Previous Message Barry Lind 2002-06-19 19:03:36 Re: [HACKERS] Milliseconds problem with PostgreSQL 7.2 jdbc driver