SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Need Total Count between Two Queries in UNION

    I am trying to get a Count of all Messages in a User's Trash. (This includes both "Incoming" messages from Others, and "Sent" messages from the User him/herself.)

    Here is my query...

    Code MySQL:
    SELECT COUNT( pm.id )
    FROM pm_recipient AS r
    INNER JOIN private_msg AS pm ON pm.id = r.message_id
    INNER JOIN member AS m_from ON m_from.id = pm.member_id_from
    WHERE r.member_id_to = ?
    AND r.deleted_on IS NOT NULL
    AND r.purged_on IS NULL
    UNION
    SELECT COUNT( pm.id )
    FROM pm_recipient AS r
    INNER JOIN private_msg AS pm ON pm.id = r.message_id
    INNER JOIN member AS m_from ON m_from.id = pm.member_id_from
    WHERE pm.member_id_from = ?
    AND sender_deleted_on IS NOT NULL
    AND sender_purged_on IS NULL


    Unfortunately, it is giving me these two separate results, and I just wanted ONE RESULT (i.e. 18)...

    Code MySQL:
    COUNT( pm.id )
    14
    4

    Is there a way to do that?

    Sincerely,


    Debbie

  2. #2
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,553
    Mentioned
    63 Post(s)
    Tagged
    0 Thread(s)
    Hi Debbie,

    I'm sticking my neck out on this one, because MySQL isn't my usual database. If anyone gives you a different answer, chances are that they are right and I am wrong.

    That said, the solution would be to simply sum the two rows of your result set. Something like this:

    Code:
    SELECT SUM(TrashCount) FROM (
    SELECT COUNT( pm.id ) AS TrashCount
    FROM pm_recipient AS r
    INNER JOIN private_msg AS pm ON pm.id = r.message_id
    INNER JOIN member AS m_from ON m_from.id = pm.member_id_from
    WHERE r.member_id_to = ?
    AND r.deleted_on IS NOT NULL
    AND r.purged_on IS NULL
    UNION
    SELECT COUNT( pm.id ) AS TrashCount
    FROM pm_recipient AS r
    INNER JOIN private_msg AS pm ON pm.id = r.message_id
    INNER JOIN member AS m_from ON m_from.id = pm.member_id_from
    WHERE pm.member_id_from = ?
    AND sender_deleted_on IS NOT NULL
    AND sender_purged_on IS NULL
    )
    Give it a try and see what you get.

    Mike

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mikl View Post
    Hi Debbie,

    I'm sticking my neck out on this one, because MySQL isn't my usual database. If anyone gives you a different answer, chances are that they are right and I am wrong.

    That said, the solution would be to simply sum the two rows of your result set. Something like this:

    Give it a try and see what you get.

    Mike
    Thanks for the suggestion, but phpMyAdmin didn't like it, and spit out...

    Code:
     MySQL said: Documentation
    #1248 - Every derived table must have its own alias

    Worst case scenario, I could bind the two Counts to variables in my Prepared Statement, and then add them up in PHP, but it just seems to me like this is not an uncommon problem, and so there must be a way to do this directly in MySQL?!

    Sincerely,


    Debbie

    P.S. What is your database then?

  4. #4
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Mike,

    Looks like you were very close with your first suggestion...

    After playing around with your query, it looks like adding "t1" at the end was all that was needed...

    Code MySQL:
    SELECT SUM( TrashCount )
    FROM (
    	SELECT COUNT( pm.id ) AS TrashCount
    	FROM pm_recipient AS r
    	INNER JOIN private_msg AS pm ON pm.id = r.message_id
    	WHERE r.member_id_to = ?
    	AND r.deleted_on IS NOT NULL
    	AND r.purged_on IS NULL
    	UNION
    	SELECT COUNT( pm.id ) AS TrashCount
    	FROM pm_recipient AS r
    	INNER JOIN private_msg AS pm ON pm.id = r.message_id
    	WHERE pm.member_id_from = ?
    	AND sender_deleted_on IS NOT NULL
    	AND sender_purged_on IS NULL
    ) t1


    With that change, I get these results...

    Code:
    SUM(TrashCount)
    18
    Thanks for almost getting me there!!


    BTW, if there is a better way to get the results that I want, then someone please speak up!!

    Sincerely,


    Debbie

  5. #5
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,553
    Mentioned
    63 Post(s)
    Tagged
    0 Thread(s)
    Debbie,

    That's right. As the error message indicates, I was missing an alias for the "derived table", that is, the part of the query between the outer parentheses. I don't know why the alias is necessary, but it does seem to be required.

    Glad you've got it working. There might well be a better way, but I'll have to leave it to someone else to show it.

    Mike

  6. #6
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know why the alias is necessary, but it does seem to be required.
    Indeed, it "just is" required. In this case there is only one source for the data so the name is not used, but it is still required, for consistency's sake I guess.

    I don't know wether the UNION is a performance choice, but you could try just adding the subqueries together:

    SELECT (SELECT 1) + (SELECT 2);

    and ofcourse you could just add the two sets of conditions together:

    WHERE
    (r.member_id_to = ?
    AND r.deleted_on IS NOT NULL
    AND r.purged_on IS NULL
    )
    OR
    (pm.member_id_from = ?
    AND sender_deleted_on IS NOT NULL
    A benchmark will tell which )

    Benchmark the options and see which works best.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by vinny42 View Post
    Benchmark the options and see which works best.
    this is good advice

    i recall at one time (earlier version?) mysql would totally barf on performance with an OR in the WHERE clause like that, which is why i've always written it as a UNION
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i recall at one time (earlier version?) mysql would totally barf on performance with an OR in the WHERE clause like that, which is why i've always written it as a UNION
    Very true, OR clauses can be a serious performance problem in many databases, but I find the performance depends very much on the phase of the moon, the color of the screensaver and probably the distribution of the options in the OR clause within the column too.

  9. #9
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,553
    Mentioned
    63 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vinny42 View Post
    I find the performance depends very much on the phase of the moon, the color of the screensaver and probably the distribution of the options in the OR clause within the column too.
    And perhaps - more importantly - the presence or absence of indexes on the relevant columns?

    Mike

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Mikl View Post
    And perhaps - more importantly - the presence or absence of indexes on the relevant columns?
    the problem is, mysql can use only one of them

    consider a situation like this --
    Code:
     WHERE (
           r.member_id_to = ?
       AND other conditions
           )
        OR (
           pm.member_id_from = ?
       AND other conditions
           )
    both r.member_id_to and pm.member_id_from could have indexes but unless the database engine makes two passes at the data, it's going to have to use a table scan to examine both columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,553
    Mentioned
    63 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the problem is, mysql can use only one of them
    I didn't know that. Thanks for the clarification.

    Mike

  12. #12
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the problem is, mysql can use only one of them
    I thought MySQL had fixed the "use only one index per table" limitation?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by vinny42 View Post
    I thought MySQL had fixed the "use only one index per table" limitation?
    that news might easily have passed right by me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •