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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 710
  • Last Modified:

Count REST of GROUP BY SQL

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
R-U-Bn
Asked:
R-U-Bn
  • 7
  • 5
  • 3
4 Solutions
 
dhobaleCommented:
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
 
R-U-BnAuthor Commented:
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
 
dhobaleCommented:
How about selecting same table twice with different names; one for your group and the other for not in that group
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
R-U-BnAuthor Commented:
Yeah, but wouldn't that be the same as doing a union, and thus not perfect in the sense of reusing memory.
0
 
R-U-BnAuthor Commented:
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
 
dhobaleCommented:
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
 
R-U-BnAuthor Commented:
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
 
racekCommented:
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
 
racekCommented:

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
 
dhobaleCommented:

You can click on my suggestion was useful. I can accept your answer.
0
 
dhobaleCommented:

You can click on my suggestion was useful. I can accept your answer.
0
 
racekCommented:
??? what do you mean?
0
 
R-U-BnAuthor Commented:
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
 
R-U-BnAuthor Commented:
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
 
R-U-BnAuthor Commented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 7
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now