Solved

Count REST of GROUP BY SQL

Posted on 2009-05-15
19
707 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
[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
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

628 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