Solved

Count REST of GROUP BY SQL

Posted on 2009-05-15
19
705 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

756 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