Home > Archive > PHP SQL > April 2006 > sql counting duplicates
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
sql counting duplicates
|
|
|
| Hy
I have this code:
------------------------------------------------------------------------
$result = mysql_query("select * from smjestaj left join grad on
grad.grad=smjestaj.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=\"headline\"><td> Grad </td></tr>";
while ($qry = mysql_fetch_array($result)) {
print "<tr><td><a href=\"" .
"lista_sa_linkovima_po_gradovima.php?grad=$qry[grad_pbroj]\"
>$qry[Naziv]</a></td>";
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 XXXXing 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 2006-04-14, 8:00 am |
| Dejan wrote:
> 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)
Is this what you want?
SELECT first_column, COUNT(*) FROM my_table GROUP BY first_column;
| |
|
| 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.googlegroups.com...
> Dejan wrote:
not[color=darkred]
>
> Is this what you want?
>
> SELECT first_column, COUNT(*) FROM my_table GROUP BY first_column;
>
| |
| Jerry Stuckle 2006-04-14, 7:00 pm |
| Dejan wrote:
> 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.googlegroups.com...
>
>
> not
>
>
>
>
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@attglobal.net
==================
| |
|
| 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
bye
Dejan
Jerry Stuckle <jstucklex@attglobal.net> wrote in message
news:efWdnQg04YVXZqLZnZ2dnUVZ_tGdnZ2d@co
mcast.com...
> Dejan wrote:
coloumn,[color=darkred]
>
> 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@attglobal.net
> ==================
| |
| robert 2006-04-18, 8:02 am |
|
| 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.
| |
| Jerry Stuckle 2006-04-18, 7:00 pm |
| 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
==================
| |
| robert 2006-04-18, 7:00 pm |
| 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.)...regardless 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@attglobal.net> wrote in message
news:h8qdnUa8fetfl9jZnZ2dnUVZ_vqdnZ2d@co
mcast.com...
| 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
| ==================
|
|
|
|
|