[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SUM up DISTINCT GROUP BY results MySQL

Posted on 2008-10-01
8
Medium Priority
?
827 Views
Last Modified: 2010-04-21
I'm using a query:

SELECT DISTINCT COUNT(o.organizationId) as rowcount
FROM CatalogListings cl, Organization o
WHERE cl.active = '1'
GROUP BY alphabetizeUnderLetter


This returns 2 results lets say, since its grouping by...

___Rowcount__
  3
  2

I want to get the numbers merged into "5" at the MySQL level through the query.
Thanks guys!
0
Comment
Question by:MattKenefick
  • 4
  • 2
  • 2
8 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22614482
this should do it:
SELECT SUM(rowcount)
FROM (
SELECT DISTINCT COUNT(o.organizationId) as rowcount 
FROM CatalogListings cl, Organization o 
WHERE cl.active = '1'
GROUP BY alphabetizeUnderLetter
)

Open in new window

0
 
LVL 6

Expert Comment

by:RemcovC
ID: 22614495
I'd say remove the GROUP BY clause
0
 
LVL 4

Author Comment

by:MattKenefick
ID: 22614596
For that query above.. I get:

#1248 - Every derived table must have its own alias


Remcov : Can't remove the GROUP BY. There is more to the query (searching) that makes it necessary.
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
LVL 4

Author Comment

by:MattKenefick
ID: 22614676
Nevermind.. if I use an "as X" identifier at the end, it works.

Thanks !
0
 
LVL 4

Author Closing Comment

by:MattKenefick
ID: 31501977
make sure to add an "AS tablename" or you'll catch errors
0
 
LVL 6

Expert Comment

by:RemcovC
ID: 22615235
MattKanefick

I thought so, but a coworker of mine was absolutely sure that it was the solution and persuaded me to post it.

Sorry for the inconvenience
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22615308
>I thought so, but a coworker of mine was absolutely sure that it was the solution and persuaded me to post it.

the problem with just removing the GROUP BY is if 2 values of alphabetizeUnderLetter have the same value of organizationId, that suggested method will return 1, and it should return 2 (1 for each Letter value)...

which leads to a "simpler" version:
SELECT DISTINCT COUNT( CONCAT( o.organizationId,alphabetizeUnderLetter)  ) as rowcount 
FROM CatalogListings cl, Organization o 
WHERE cl.active = '1'

Open in new window

0
 
LVL 4

Author Comment

by:MattKenefick
ID: 22615354
Remcov: No problem. All possibly solutions are welcome. Who knows, maybe a wrong answer today will be the right answer tomorrow.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question