Re: Using AND in query

On Sat, Aug 07, 2010 at 12:40:41PM -0700, aravind chandu wrote:
> Hello every one,
>
> I have encountered a problem while working .I have a sample table with the
> following data
>
>
> TID Date Item
> T100 8/1/2010 Laptop
> T100 8/1/2010 Desktop
> T101 8/1/2010 Laptop
> T102 8/1/2010 Desktop
> T103 8/2/2010 Laptop
> T103 8/2/2010 Desktop
> T104 8/2/2010 Laptop
>  
> need the data when a person bought laptop & desktop on the sameday.

This is actually relatively straight-forward using modern PostgreSQL.
Rather than counting, use direct aggregation to compare, so:

SELECT TID, "Date"
FROM "table"
GROUP BY TID, "Date"
HAVING ARRAY['Laptop','Desktop'] <@ array_agg(item);

That last line checks whether the array created by array_agg contains
at least the elements Laptop and Desktop. If you need an "equals"
comparison rather than the above "contains or equals", you can sort
both arrays canonically using the array_sort function below and then
compare them with "=".

CREATE OR REPLACE FUNCTION array_sort(ANYARRAY)
RETURNS ANYARRAY
LANGUAGE SQL
AS $$
SELECT ARRAY(SELECT * FROM unnest($1) ORDER BY 1);
$$;

The "=" query would look like this:

SELECT TID, "Date"
FROM "table"
GROUP BY TID, "Date"
HAVING array_sort(ARRAY['Laptop','Desktop']) = array_sort(array_agg(item));

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate