SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql - Count with two group by

    Hi,
    my table
    CREATE TABLE IF NOT EXISTS si_stats_access (
    id BIGINT UNSIGNED NOT NULL auto_increment,
    session_id CHAR(64) NOT NULL,
    fb_id BIGINT unsigned DEFAULT NULL,
    domain_id INT UNSIGNED NOT NULL,
    browser_id SMALLINT NOT NULL,
    so_id SMALLINT NOT NULL,
    device_id SMALLINT NOT NULL,
    referer_id SMALLINT DEFAULT NULL,
    ip VARCHAR(15) NOT NULL,
    log_fb_datetime DATETIME DEFAULT NULL,
    access_start_datetime DATETIME NOT NULL,
    access_end_datetime DATETIME DEFAULT NULL,
    country_code CHAR(2) DEFAULT NULL,
    country_name VARCHAR(255) DEFAULT NULL,
    region_code TINYINT DEFAULT NULL,
    region_name VARCHAR(255) DEFAULT NULL,
    city VARCHAR(255) DEFAULT NULL,
    lat FLOAT( 10, 6 ) DEFAULT 0 ,
    lng FLOAT( 10, 6 ) DEFAULT 0,
    PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    my query

    SELECT 1 as stats_type_id, domain_id, DATE (access_start_datetime) as date, COUNT (id) as value
    FROM si_stats_access
    GROUP BY domain_id, DATE(access_start_datetime);

    it doesn't work I get only a domain id

    as usual can you help me please ?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by whisher View Post
    it doesn't work I get only a domain id
    could you elaborate on this a bit please

    what does "it doesn't work" mean?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    could you elaborate on this a bit please

    what does "it doesn't work" mean?
    for example instead of
    1 1 2013-03-11 100
    1 2 2013-03-11 100
    1 3 2013-03-11 100
    I got
    1 1 2013-03-11 100
    I got only the rows for a domain

    btw this seems to work but I don't see the point
    SELECT 1 AS stats_type_id, domain_id, DATE( access_start_datetime ) AS DATE, COUNT( id ) AS value
    FROM si_stats_access
    GROUP BY DATE( access_start_datetime ) , domain_id

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by whisher View Post
    for example instead of
    1 1 2013-03-11 100
    1 2 2013-03-11 100
    1 3 2013-03-11 100
    I got
    1 1 2013-03-11 100
    I got only the rows for a domain
    i'm sorry, i can't understand this -- the 2nd column here is your domain, and it would not have completely dropped domains 2 and 3, since domain is in the GROUP BY

    by the way, the rows that a GROUP BY query produces are ~not~ the same as the rows in the table

    Quote Originally Posted by whisher View Post
    btw this seems to work but I don't see the point
    SELECT 1 AS stats_type_id, domain_id, DATE( access_start_datetime ) AS DATE, COUNT( id ) AS value
    FROM si_stats_access
    GROUP BY DATE( access_start_datetime ) , domain_id
    this is exactly the same as the other query, just with the order of the GROUP BY columns reversed, which makes no difference to the actual results produced... except that they might be presented in a different order
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry buddy it's my fault the query I posted
    was to build a view and I query the view
    with phpmyadmin that don't display all the
    rows ^^

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by whisher View Post
    Sorry buddy it's my fault the query I posted
    was to build a view and I query the view
    with phpmyadmin that don't display all the
    rows ^^
    oh
    r937.com | rudy.ca | 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
  •