Posted by Jerry Stuckle on 10/10/69 11:45

robert wrote:
> | The solution is:
> |
> | select grad_pbroj, Naziv, COUNT(*) as brojac from smjestaj left join grad
> on
> | grad.grad=smjestaj.grad_pbroj group by grad_pbroj order by grad_pbroj ASC
>
>
> i don't think so...that should cough up a nasty error as "naziv" is not
> handled in the group by clause nor is it part of an aggrigate function...and
> that's just me eye-balling it for .5 seconds.
>
> btw...people who DONT format their inline sql should be shot! which is more
> manageable, the above or:
>
> SELECT s.grad_pbroj ,
> s.Naziv ,
> COUNT(*) brojac
> FROM smjestaj s
> LEFT JOIN grad g ON
> g.grad = s.grad_pbroj
> GROUP BY s.grad_pbroj ,
> s.Naziv
> ORDER BY s.grad_pbroj ASC
>
>
> ** and if this query is to return the number of duplicates, then there
> should either be a where or having clause that only returns a row if the
> count is > 1.
>
> but that's just me following real-world, professional standards. the former
> should just be all on one line for christ' sake.
>
>

Close - you would need a HAVING clause:

SELECT s.grad_pbroj, s.Naziv, COUNT(*) AS brojac
FROM smjestaj s
LEFT JOIN grad g ON g.grad = s.grad_pbroj
GROUP BY s.grad_pbroj,s.Naziv
HAVING COUNT(*) > 1
ORDER BY s.grad_pbroj ASC

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]