Topic awaiting preservation: MySQL help (Page 1 of 1)

Hey all, got a problem. I would appreciate any and all help.

Thanks!

"The current or built-in procedure/counter basically adds an increment of 1 for every record that qualifies. I would like to be able to instruct it, instead, to add to its tally the value contained in a particular column of that record instead of just 1. In other words, count distinct does a sum of 1s, I want it to sum values specified in the record.

In terms of SQL, syntax...

here's how it is now:
SELECT city, Count(DISTINCT(nights)) FROM trips GROUP BY city;

and this is what I would want:
SELECT city, Count(DISTINCT(nights) USE(mycolumn)) FROM trips GROUP BY city;

...the USE clause would point to the column to be added.

now here is the regular count aggregate query:

SELECT sex, reason, city, Count(DISTINCT punterID)
FROM (punters inner join trips ON punters.ID=trips.punterID)
INNER JOIN stays ON trips.ID=stays.tripID
GROUP BY sex, reason, city;

what i need instead of it summing 1s to get the count is for it to sum
value in the column called "wgt" in the "punters" table.

here is the slow alternative that can do it:

SELECT sex, reason, city, Sum(wgt) FROM
(SELECT punterID, reason, city, Count(stayRslt) AS tripRslt FROM
(SELECT tripID, city, Count(city) AS stayRslt FROM stays GROUP BY tripID, city) AS stayQ
INNER JOIN trips ON stayQ.tripID=trips.ID
GROUP BY punterID, reason) AS tripQ
INNER JOIN punters ON tripQ.punterID=punters.ID
GROUP BY sex, reason, city;


..but it takes too long with 2 million records."

Casey / AT
Personal
Song Lyrics
Family Recipes

Anyone?



Casey / AT
Personal
Song Lyrics
Family Recipes

aeh... I'm pretty busy these days.

how about select distict city, mycolumn * nights from trips group by city, night; ?