![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
![]() |
Fri, Mar 13 2009 12:07 PM | Permanent Link |
Uli Becker | Hi,
I want to query this table (just a sample): Name : varchar Black: boolean; Blue: boolean; PercentageBlack: float; PercentageBlue: float; and would like to get this result: Name Total CountOfBlack CountOfBlue PercentageBlack PercentageBlue xy 300 60 240 20 80 Generally no problem, but: Is it possible to to that in one query? Regards Uli |
Fri, Mar 13 2009 3:35 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Uli,
<< I want to query this table (just a sample): Name : varchar Black: boolean; Blue: boolean; PercentageBlack: float; PercentageBlue: float; and would like to get this result: Name Total CountOfBlack CountOfBlue PercentageBlack PercentageBlue >> Try this: SELECT Name, COUNT(*) AS Total, COUNT(IF(Black,1,0)) AS CountOfBlack, COUNT(IF(Blue,1,0)) AS CountOfBlue FROM MyTable GROUP BY Name However, I'm not sure what you want to do with the percentages - average them ? Also, are the percentages actually in the source table ? -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Mar 13 2009 4:06 PM | Permanent Link |
Ulrich Becker | Tim
> SELECT Name, > COUNT(*) AS Total, > COUNT(IF(Black,1,0)) AS CountOfBlack, > COUNT(IF(Blue,1,0)) AS CountOfBlue > FROM MyTable > GROUP BY Name Looks good, but doesn't seem to work. CountOfBlack and CountOfBlue have the same value as Total. > However, I'm not sure what you want to do with the percentages - average > them ? Also, are the percentages actually in the source table ? Just calculated values, not important for the query, I should have omitted them. Uli |
Fri, Mar 13 2009 4:33 PM | Permanent Link |
Ulrich Becker | Tim,
how about this: select Name, count(*) as Total, sum(cast(black as integer)) as CountBlack, sum(cast(blue as integer)) as CountBlue from test group by name; Seems to do the trick. ![]() Uli |
Mon, Mar 16 2009 3:14 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Uli,
<< Looks good, but doesn't seem to work. CountOfBlack and CountOfBlue have the same value as Total. >> Duh, I used COUNT() when I should have used SUM(): SELECT Name, COUNT(*) AS Total, SUM(IF(Black,1,0)) AS CountOfBlack, SUM(IF(Blue,1,0)) AS CountOfBlue FROM MyTable GROUP BY Name or you could use this: SELECT Name, COUNT(*) AS Total, COUNT(IF(Black,Black,NULL)) AS CountOfBlack, COUNT(IF(Blue,Blue,NULL)) AS CountOfBlue FROM MyTable GROUP BY Name -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Mar 16 2009 4:13 PM | Permanent Link |
Ulrich Becker | Tim
> Duh, I used COUNT() when I should have used SUM(): And with sum() it works fine, off course. ![]() out myself!). Regards Uli |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |