Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 821
  • 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
Industry Leaders: 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!

 
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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