Re: Counting distinct rows in SQL

On Thu, 20 Jan 2005 11:07:07 -0800, nathan.cutmore_at_burohappold.com wrote:

> I am trying to develop an SQL query that turns this table...
> ---------------------------------------------------------------
> item_id item_title location_id location
> ---------------------------------------------------------------
> 476 Abfab 2 Dubai
> 476 Abfab 6 London
> 476 Abfab 6 London
> 476 Abfab 3 Paris
> ---------------------------------------------------------------
[...]
> Into this...
> ---------------------------------------------------------------
> item_id item_title location_id location copies
> ---------------------------------------------------------------
> 476 Abfab 2 Dubai 1
> 476 Abfab 6 London 2
> 476 Abfab 3 Paris 1
> ---------------------------------------------------------------

Let's say the table is called 'itloc'. Then try this:

  SELECT *,COUNT(*)
    FROM itloc
GROUP BY item_id,item_title,location_id,location;

-- 
Greetings from Troels Arvin, Copenhagen, Denmark