SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Feb 2014
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL count multiple columns and sum the total occurrence

    Dear All,


    I read from the Internet saying that MySQL UNION ALL is faster than UNION, and I have a MyDraw table as follows:
    Code:
     DRAWNO    PRIZENO FIRSTD  SECONDD THIRDD  FOURTHD
    M123/14     8458    8       4       5       8
    M123/14     7214    7       2       1       4
    M123/14     1640    1       6       4       0
    M123/14     2103    2       1       0       3
    M123/14     2557    2       5       5       7
    M123/14     2975    2       9       7       5
    M123/14     3767    3       7       6       7
    M123/14     4409    4       4       0       9
    M123/14     4887    4       8       8       7
    M123/14     5844    5       8       4       4
    I would like to count and sum them UP how many times of occurrence of digit from 0 to 9 (0,1,2,3,4,5,6,7,8,9) for 4 different differenct columns, which is FIRSTD, SECONDD, THIRDD, FORTHD ...

    Expected result is ...
    Code:
    DRAWNO   [D0][D1][D2][D3][D4][D5][D6][D7][D8][D9]
    M123/14   3   3   4   2   8   5   2   6   5   2
    I have posted question to stackexchange, as follows: http://dba.stackexchange.com/questio...tal-occurrence

    Solution given by using UNION, which works fine; but one of the expert suggest to use UNION ALL.

    I tried to figure out how, and read MySQL from the Internet, it seems that all example given is UNION ALL for different tables; but for my case, it is same table.

    I would like to use UNION ALL, if possible as it is faster, I assume.

    Thanks in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it's not just a question of performance

    UNION removes duplicates!!!
    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
  •