Re: SQL Question

Brady:

One method is to create a view of this table and join to it. The code looks something like this (I renamed table "A" to "foo"):

--BEGIN-- drop view foo_view;
create view foo_view as

select	person,
	count(*) cnt_tool
from	foo
where	tool = 'Hammer'
group by
	person;

select	f.person,
	nvl(fv.cnt_tool,0) cnt_tool
from	foo_view fv
	foo f
where	f.person = fv.person (+)
and	f.city = 'Dallas';

drop view foo_view;

--END--

• Know your data ** Do you need to you the "distinct" clause anywhere, e.g. count number of distinct tools or include all rows?

Don't forget to drop views if they are not needed for any other program. They waste a lot of space over time, if you don't maintain your system closely.

The "drop view" in the beginning **can** be dangerous--be sure to use names not used by other programs/other people!

-Peter

On 19 May 1996 03:03:28 GMT, bradyd_at_ti.com (Brady D'Andrea) wrote:

>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')
> GROUP BY person;
>(I need the IN() for my queries since I'll be searching for several items)
>
>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
>
Received on Sun May 19 1996 - 00:00:00 CEST