sql counting duplicates

[Dejan]

Hy

I have this code:
------------------------------------------------------------------------
$result = mysql_query("se lect * from smjestaj left join grad on
grad.grad=smjes taj.grad_pbroj order by grad_pbroj ASC", $db) or die ("Could
not read data because ".mysql_error() );


// print the data in a table
if (mysql_num_rows ($result)) {

print "<table cellspacing=0 border=1 width=\"25%\" class=\"redovi\ ">\n";
print "<tr class=\"headlin e\"><td> Grad </td></tr>";
while ($qry = mysql_fetch_arr ay($result)) {
print "<tr><td><a href=\"" .
"lista_sa_linko vima_po_gradovi ma.php?grad=$qr y[grad_pbroj]\"[color=blue]
>$qry[Naziv]</a></td>";[/color]
print "</tr>\n";
}
print "</table>\n";
}
mysql_close($db );

------------------------------------------------------------------------
Table smjestaj has coloumns:
id | Name | grad_pbroj | ...

Table grad has coloumns:
grad_pbroj | Naziv|

Now i wanna display all the grad_pbroj coloumns from 'smjestaj', buth not
displaying duplicates, just to count them, and put the count behind the
'grad_pbroj' coloumn.

Result:
Naziv_first (3)
Naziv_second (1)
Naziv_third (8)

I nead full solution, because i have tried everythig and either sql of php
displaying is fucking me.
I have tried all of that group by, count sql syntax, and i allways get
error.
Anyone cann help me plz.
Dejan

ps. sorry to all the people i have asked this before, buth not giving full
description (Arjen, Jerry Stuckle).

[Sjoerd]

Re: sql counting duplicates

Dejan wrote:[color=blue]
> Now i wanna display all the grad_pbroj coloumns from 'smjestaj', buth not
> displaying duplicates, just to count them, and put the count behind the
> 'grad_pbroj' coloumn.
>
> Result:
> Naziv_first (3)
> Naziv_second (1)
> Naziv_third (8)[/color]

Is this what you want?

SELECT first_column, COUNT(*) FROM my_table GROUP BY first_column;

[Dejan]

Re: sql counting duplicates

Yeah, i know that, buth i have left join...order by ... group by.

And i have sql syntax error, and don't know how to display related coloumn,
and number of duplicates.


Sjoerd <sjoerder@gmail .com> wrote in message
news:1145009057 .659424.240850@ u72g2000cwu.goo glegroups.com.. .[color=blue]
> Dejan wrote:[color=green]
> > Now i wanna display all the grad_pbroj coloumns from 'smjestaj', buth[/color][/color]
not[color=blue][color=green]
> > displaying duplicates, just to count them, and put the count behind the
> > 'grad_pbroj' coloumn.
> >
> > Result:
> > Naziv_first (3)
> > Naziv_second (1)
> > Naziv_third (8)[/color]
>
> Is this what you want?
>
> SELECT first_column, COUNT(*) FROM my_table GROUP BY first_column;
>[/color]

[Jerry Stuckle]

Re: sql counting duplicates

Dejan wrote:[color=blue]
> Yeah, i know that, buth i have left join...order by ... group by.
>
> And i have sql syntax error, and don't know how to display related coloumn,
> and number of duplicates.
>
>
> Sjoerd <sjoerder@gmail .com> wrote in message
> news:1145009057 .659424.240850@ u72g2000cwu.goo glegroups.com.. .
>[color=green]
>>Dejan wrote:
>>[color=darkred]
>>>Now i wanna display all the grad_pbroj coloumns from 'smjestaj', buth[/color][/color]
>
> not
>[color=green][color=darkred]
>>>displaying duplicates, just to count them, and put the count behind the
>>>'grad_pbro j' coloumn.
>>>
>>>Result:
>>>Naziv_firs t (3)
>>>Naziv_seco nd (1)
>>>Naziv_thir d (8)[/color]
>>
>>Is this what you want?
>>
>>SELECT first_column, COUNT(*) FROM my_table GROUP BY first_column;
>>[/color]
>
>
>[/color]

Well, post your attempt(s) and maybe we can see what's wrong!

Sjoerd showed you how to do it. We can't do much more with the information
you've provided.


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

[Dejan]

Re: sql counting duplicates

The solution is:

select grad_pbroj, Naziv, COUNT(*) as brojac from smjestaj left join grad on
grad.grad=smjes taj.grad_pbroj group by grad_pbroj order by grad_pbroj ASC

bye
Dejan


Jerry Stuckle <jstucklex@attg lobal.net> wrote in message
news:efWdnQg04Y VXZqLZnZ2dnUVZ_ tGdnZ2d@comcast .com...[color=blue]
> Dejan wrote:[color=green]
> > Yeah, i know that, buth i have left join...order by ... group by.
> >
> > And i have sql syntax error, and don't know how to display related[/color][/color]
coloumn,[color=blue][color=green]
> > and number of duplicates.
> >
> >
> > Sjoerd <sjoerder@gmail .com> wrote in message
> > news:1145009057 .659424.240850@ u72g2000cwu.goo glegroups.com.. .
> >[color=darkred]
> >>Dejan wrote:
> >>
> >>>Now i wanna display all the grad_pbroj coloumns from 'smjestaj', buth[/color]
> >
> > not
> >[color=darkred]
> >>>displaying duplicates, just to count them, and put the count behind the
> >>>'grad_pbro j' coloumn.
> >>>
> >>>Result:
> >>>Naziv_firs t (3)
> >>>Naziv_seco nd (1)
> >>>Naziv_thir d (8)
> >>
> >>Is this what you want?
> >>
> >>SELECT first_column, COUNT(*) FROM my_table GROUP BY first_column;
> >>[/color]
> >
> >
> >[/color]
>
> Well, post your attempt(s) and maybe we can see what's wrong!
>
> Sjoerd showed you how to do it. We can't do much more with the[/color]
information[color=blue]
> you've provided.
>
>
> --
> =============== ===
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attgl obal.net
> =============== ===[/color]

[robert]

Re: sql counting duplicates


| The solution is:
|
| select grad_pbroj, Naziv, COUNT(*) as brojac from smjestaj left join grad
on
| grad.grad=smjes taj.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.

[Jerry Stuckle]

Re: sql counting duplicates

robert wrote:[color=blue]
> | The solution is:
> |
> | select grad_pbroj, Naziv, COUNT(*) as brojac from smjestaj left join grad
> on
> | grad.grad=smjes taj.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.
>
>[/color]

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@attgl obal.net
=============== ===

[robert]

Re: sql counting duplicates

and...you'd want to put the standard inline sql formatting *back in*...each
column named and on a seperate line, delimiting columns aligned, table names
aliased and aligned, reserved words in caps, conditions aligned (=, !=, IN,
NOT IN, etc.)...regardl ess of select, insert, update, etc.

and that's not just *my* pet-peave. the more complex the query, view, proc,
or udf the more the differences are noticed and appreciated.


"Jerry Stuckle" <jstucklex@attg lobal.net> wrote in message
news:h8qdnUa8fe tfl9jZnZ2dnUVZ_ vqdnZ2d@comcast .com...
| robert wrote:
| > | The solution is:
| > |
| > | select grad_pbroj, Naziv, COUNT(*) as brojac from smjestaj left join
grad
| > on
| > | grad.grad=smjes taj.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@attgl obal.net
| =============== ===