aggregate/sum with distinct conditional

[foto66]

i am in urgent need of some help.

consider the following table:

col1, col2, col3
1, 1.25, 1
1, 1.25, 2
1, 1.25, 2
2, 0.75, 1
2, 0.75, 1
2, 0.75, 1
3, 1.25, 1
3, 1.25, 1
3, 1.25, 1
3, 1.25, 2

from this "theoretical" SQL statement

SELECT col3, IF(DISTINCT(col1),SUM(col2),SUM(0)) As mySum FROM table1 GROUP BY col3

or perhaps this one (neither actually works)

SELECT col3, SUM(IF(DISTINCT(col1), col2, 0)) As mySum FROM table1 GROUP BY col3

result would be...

col3, mySum
1, 3.25
2, 2.50

i have a solution that invoves sub-queries but it is too slow (so please don't suggest that route). is there some little bit of syntactic logic i'm missing to get my "if" clause to work?

one more thing: would it be easy enough to write a UDF to handle this?

 

[rac2]

...some little bit of syntactic logic..."

IF(expr1, expr2,expr3) applies to values within a single row, the expressions all use data from the same row.

DISTINCT is a keyword, not a function. It applies to a set of rows.


WARNING! Do not read the following code as it contains a subquery.

SELECT col3, SUM(col2) AS mySum
FROM 
   (SELECT DISTINCT col1, col2, col3
    FROM table1)
GROUP BY col3

Subquery produces this set of rows.
col1, col2, col3
1, 1.25, 1
1, 1.25, 2
2, 0.75, 1
3, 1.25, 1
3, 1.25, 2

 

[foto66]

this is not looking good. perhaps you could help me make this query faster then (i tried some of the logical things, but it just got slower):

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;

here is the table syntax:

CREATE TABLE `punters` (
`ID` int(11) NOT NULL default '0',
`sex` tinyint(4) NOT NULL default '0',
`age` tinyint(4) NOT NULL default '0',
`year` tinyint(1) NOT NULL default '0',
`wgt` double NOT NULL default '0',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `trips` (
`ID` int(11) NOT NULL default '0',
`punterID` int(11) NOT NULL default '0',
`reason` tinyint(4) NOT NULL default '0',
`transport` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`ID`),
KEY `ix_punterID` (`punterID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `stays` (
`ID` int(11) NOT NULL default '0',
`tripID` int(11) NOT NULL default '0',
`city` tinyint(4) NOT NULL default '0',
`hotdog` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`ID`),
KEY `ix_tripID` (`tripID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

 

[azzazzello]

have you tried indexing things?

 

[r937]

could you perhaps describe what you want in english?

this doesn't make any sense at all --

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;

r937.com | rudy.ca