Solved

Count REST of GROUP BY SQL

Posted on 2009-05-15
19
698 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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 …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
A short film showing how OnPage and Connectwise integration works.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

937 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now