Re: SQL Question

bradyd_at_ti.com (Brady D'Andrea) wrote:

You can achive this by using union. See below for detail.

>Hi,
 

>For the life of me, I can't figure out the proper SQL statement to return the
>information I need. I'm searching a table based on two criteria. I need a
>count of all rows returned that match the second criteria grouped by the first
>critera, but I need items from the first criteria that don't match the second
>to return a count of 0
 

>Using the following table as an example:
> TABLE A: CITY PERSON TOOL
> ------ ------ ------
> DALLAS Mike Hammer
> DALLAS Kim Hammer
> DALLAS John WRENCH
> DALLAS Kim Hammer
> HOUSTON Bill Hammer
 

>The best SQL I can come up with is:
> SELECT person, COUNT(tool) FROM A
> WHERE city IN ('DALLAS') AND tool IN ('Hammer')

       SELECT person, COUNT(tool) FROM A
       WHERE city IN ('DALLAS') AND tool IN ('Hammer')
       Group by Person
       union
       Select person , 0 from A
       where city in ( 'DALLAS' ) and tool not in ( 'Hammer' )
        Group by Person



>        GROUP BY person;

>This produces the following output:
> PERSON COUNT(TOOL)
> ---------- -----------
> Kim 2
> Mike 1
 

>But what I need is:
> PERSON COUNT(TOOL)
> ---------- -----------
> John 0 <--- I need this zero value
> Kim 2
> Mike 1
>
>Any advice on generating the proper SQL statement would be MUCH appreciated!
 

>Thanks in Advance,
>Brady
>bradyd_at_ti.com

Disclaimer : If my employer shares my views, I will be surprised Received on Tue May 21 1996 - 00:00:00 CEST