Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SUM up DISTINCT GROUP BY results MySQL

Posted on 2008-10-01
8
Medium Priority
?
813 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

715 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