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
Solved

SUM up DISTINCT GROUP BY results MySQL

Posted on 2008-10-01
8
801 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 500 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

828 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