• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 839
  • Last Modified:

SUM up DISTINCT GROUP BY results MySQL

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
MattKenefick
Asked:
MattKenefick
  • 4
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
RemcovCCommented:
I'd say remove the GROUP BY clause
0
 
MattKenefickAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
MattKenefickAuthor Commented:
Nevermind.. if I use an "as X" identifier at the end, it works.

Thanks !
0
 
MattKenefickAuthor Commented:
make sure to add an "AS tablename" or you'll catch errors
0
 
RemcovCCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
MattKenefickAuthor Commented:
Remcov: No problem. All possibly solutions are welcome. Who knows, maybe a wrong answer today will be the right answer tomorrow.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now