Solved

Count REST of GROUP BY SQL

Posted on 2009-05-15
19
704 Views
Last Modified: 2012-05-07
I want to know if there is a kind of "REST" function, syntax or any solution to display the rest of the group by query at the bottom row, meaning all the entries of that did NOT result inthe normal group by query.
I find it quite hard to explain, let me example it:

When I do this query:
________________________________________________________________________________
SELECT tag_name, count(*) AS C FROM tags AS t
LEFT JOIN tagposts AS tp ON t.tag_id=tp.tag_id
WHERE tag_name IN ("milieu","dieren","gezondheid","nz","eva","evenementen","audio","video")
GROUP BY tag_id
________________________________________________________________________________
I get:
________________________________________________________________________________
dieren            5
video            18
gezondheid      43
nz                  6
milieu            27
eva                  303
________________________________________________________________________________
What I want is this:
________________________________________________________________________________
dieren            5
video            18
gezondheid      43
nz                  6
milieu            27
eva                  303
rest                  1600
________________________________________________________________________________
 (in 1 query, please!)

So, the "rest" in this case are all tags that DON'T have the  tag_name "milieu","dieren","gezondheid","nz","eva","evenementen","audio" or "video"!

(It makes me think that I am looking for something like WITH ROLLUP, but with the negative query, heh, if this makes any direction to where i want to go)

I know you can do a union with the negative query, but, being perfectionist, I am wondering if there isn't a solution where the mysql engine already uses the temporary memory and stuff from the (positive) ongoing grouping.

0
Comment
Question by:R-U-Bn
  • 7
  • 5
  • 3
19 Comments
 
LVL 5

Assisted Solution

by:dhobale
dhobale earned 200 total points
ID: 24395825
SELECT tag_name, count(*) AS C FROM tags AS t
LEFT JOIN tagposts AS tp ON t.tag_id=tp.tag_id
WHERE tag_name IN ("milieu","dieren","gezondheid","nz","eva","evenementen","audio","video") or tag_name ISNULL as Rest

GROUP BY tag_id
0
 
LVL 2

Author Comment

by:R-U-Bn
ID: 24396421
Hmm, doesn't work.
Mysql gives an error even.  I believe aliasses are not allowed in WHERE?
I tried other combinations with this "sense of solution", but I fail.
For example:
________________________________________________
SELECT tag_name, count(*) AS C, ISNULL(tag_name) AS Rest FROM jos_deli_t AS t
LEFT JOIN jos_deli_tp AS tp ON t.tag_id=tp.tag_id
WHERE tag_name IN ("milieu","dieren","gezondheid","nz","eva","evenementen","audio","video") OR ISNULL(tag_name)
GROUP BY t.tag_id
__________________________________________________

But I wonder if it would work at all since there isn't any tag_name that is NULL..
I mean, this:
_____________________________
SELECT tag_name , count(*) AS C FROM tags AS t
LEFT JOIN tagposts  AS tp ON t.tag_id=tp.tag_id
WHERE  ISNULL(tag_name)
GROUP BY tag_name
______________________________
gives no results (no records).  Or has this something to do with the group by?...

Hmmm...help :-)


0
 
LVL 5

Expert Comment

by:dhobale
ID: 24396511
How about selecting same table twice with different names; one for your group and the other for not in that group
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 2

Author Comment

by:R-U-Bn
ID: 24396540
Yeah, but wouldn't that be the same as doing a union, and thus not perfect in the sense of reusing memory.
0
 
LVL 2

Accepted Solution

by:
R-U-Bn earned 0 total points
ID: 24396564
FOUND IT!!!
__________________________________________________
SELECT IF(tag_name IN ("milieu","dieren","gezondheid","nz","eva","evenementen","audio","video"),tag_name,"rest") AS tn, count(*) AS C FROM tags AS t
LEFT JOIN tagposts  AS tp ON t.tag_id=tp.tag_id
GROUP BY tn
______________________________________________________


Your first query made me think it up. Not that again!  How can I give you points now and accept my answer as answer?  I hate this.
0
 
LVL 5

Expert Comment

by:dhobale
ID: 24396607
In that case, the best solution would be to create a stored procedure or a new table to group your categories and then add it to above query
0
 
LVL 2

Author Comment

by:R-U-Bn
ID: 24397443
Problem is that I need to go deeper and deeper, and the new table would not be so easy to make nor performant.... I think..

Consider the following queries (not optimized yet) that would then be called:
___________________________________________________________
SELECT tag_name AS tn, count(*) AS C FROM tags AS t
LEFT JOIN jos_deli_tp AS tp ON t.tag_id=tp.tag_id

WHERE tp.post_id IN (SELECT post_id FROM tagposts AS tp3 WHERE tp3.tag_id=166  )
AND  tp.post_id IN (SELECT post_id FROM tagposts AS tp3 WHERE tp3.tag_id=176  )
AND  tp.post_id IN (SELECT post_id FROM tagposts  AS tp3 WHERE tp3.tag_id=23  )


AND t.tag_id!=166
AND t.tag_id!=176
AND t.tag_id!=23


GROUP BY tn
______________________________________________________________
0
 
LVL 14

Assisted Solution

by:racek
racek earned 170 total points
ID: 24398281
SELECT IF(tag_name IN ("milieu","dieren","gezondheid",
               "nz","eva","evenementen","audio","video"), tag_name, 'Other') as tag_name ,
           count(*) AS C
FROM tags AS t
LEFT JOIN tagposts AS tp ON t.tag_id=tp.tag_id
GROUP BY 1;
0
 
LVL 14

Assisted Solution

by:racek
racek earned 170 total points
ID: 24398337

SELECT tag_name AS tn, count(*) AS C 
FROM tags AS t
JOIN tagposts AS tp3 
  ON t.tag_id = tp3.tag_id 
    AND tp3.tag_id IN (23,166,176  )
    AND t.tag_id NOT IN(166,176,23)
LEFT JOIN jos_deli_tp AS tp ON t.tag_id=tp.tag_id
GROUP BY tn

Open in new window

0
 
LVL 5

Expert Comment

by:dhobale
ID: 24398403

You can click on my suggestion was useful. I can accept your answer.
0
 
LVL 5

Expert Comment

by:dhobale
ID: 24398416

You can click on my suggestion was useful. I can accept your answer.
0
 
LVL 14

Expert Comment

by:racek
ID: 24398652
??? what do you mean?
0
 
LVL 2

Author Comment

by:R-U-Bn
ID: 24440032
Due to the nature of my job, I have to postpone this.  But I'll wil lbe back on your answers!
I marked a reminder.  So, please be patient, dear helpers.
0
 
LVL 2

Author Comment

by:R-U-Bn
ID: 24734966
I want to accept multiple solutions, but I can't find it anymore!
Is that because of the admin request?  Please unlock it or help me find it and I'll split points.  (I'll continue my research in another question later)
0
 
LVL 2

Author Comment

by:R-U-Bn
ID: 24746363
Damn, I already managed it via my obkjection, just as you said, but now I have to do it again.
Wel, serves me right for not mentioning that, I guess.
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

Suggested Solutions

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

792 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