1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

How to get total entries for each row??

Discussion in 'MySQL' started by medialab, Jan 20, 2020.

  1. #1
    Im really stumped on this one, I have an area on my site where users can enter different contest drawings, I have 2 tables, 1 for the prizes themselves and 1 for the entries.

    I can pull the prizes and the TOTAL entries, but I am having a hard time figuring out how to pull a specific users entries into each drawing. Here is an example of my tables (which can be changed, they are not used any where else on the site).

    prizes (table name)

    prize_id (key), prize_status, prize_name, prize_image
    --------------- ------------ ------------ -----------
    1, 1, iPhone, image.jpg
    2, 1, Backpack, image2.jpg
    3, 1, Gift Card, image3.jpg
    4, 1, Candle, image4.jpg
    5, 1, Something, image5.jpg

    entries (table name)

    entry_id (key), prize_id, user_id, outcome
    ---------------- --------- --------- ----------
    1, 1, 1000, 1
    2, 1, 1000, 1
    3, 1, 1001, 1
    4, 2, 1000, 1
    5, 1, 1000, 1

    This is my current SQL statement:

    SELECT prizes.prize_id, prizes.prize_name, prizes.prize_image, SUM(outcome) AS total_entries FROM prizes LEFT OUTER JOIN entries ON (prizes.prize_id = entries.prize_id) WHERE prizes.prize_status = 1 GROUP BY prizes.prize_id;
    SEMrush
    Ideally, I would like to have 1 statement with all the info. I am jaime SUM to get the total entries, easy enough - but I can't think of a way to get the number of entries for each row the user has entered. For example if the users ID was 1000:

    prize_id, prize_name, prize_image, total_entries, user_entries
    --------- ------------- ------------- -------------- --------------
    1, iPhone, image.jpg, 4, 3
    2, Backpack, image2.jpg, 1, 1
    3, Gift Card, image3.jpg, 0, 0
    4, Candle, image4.jpg, 0, 0
    5, Something, image5.jpg, 0, 0

    Any help would be greatly appreciated!!
     
    Last edited: Jan 20, 2020
    medialab, Jan 20, 2020 IP
    SEMrush
  2. sarahk

    sarahk iTamer Staff

    Messages:
    25,566
    Likes Received:
    3,563
    Best Answers:
    103
    Trophy Points:
    665
    #2
    Hi

    I'm not at all sure about your database design but I'll leave that for another post. I've made a change or two where I just couldn't leave it as is :)

    Check out http://sqlfiddle.com/#!9/ad2910/4 and have a play with the queries

    CREATE TABLE IF NOT EXISTS `contests` (
      `id` int(6)  NOT NULL,
      `status` varchar(20)  NOT NULL,
      `prize_name` varchar(200) NOT NULL,
      `prize_image` varchar(200) not null,
      PRIMARY KEY (`id`)
    ) DEFAULT CHARSET=utf8;
    
    INSERT INTO `contests` (`id`, `status`, `prize_name`, `prize_image`) VALUES
      ('1', 'Finished', 'iPhone', 'image1.jpg'),
      ('2', 'Active', 'Backpack', 'image2.jpg'),
      ('3', 'Active', 'Gift Card', 'image3.jpg'),
      ('4', 'Draft', 'Candle', 'image4.jpg'),
      ('5', 'Draft', 'Something', 'image5.jpg');
    
    CREATE TABLE IF NOT EXISTS `entries` (
      `id` int(6)  NOT NULL,
      `contest_id` int(6)  NOT NULL,
      `user_id` int(6)  NOT NULL,
      `outcome` varchar(200) not null,
      `entry_image` varchar(200) null,
      PRIMARY KEY (`id`)
    ) DEFAULT CHARSET=utf8;
    
    INSERT INTO `entries` (`id`, `contest_id`, `user_id`, `outcome`, `entry_image`) VALUES
      ('1', 1, 1000, 0, 'entry1.jpg'),
      ('2', 1, 1000, 1, 'entry2.jpg'),
      ('3', 1, 1001, 0, 'entry3.jpg'),
      ('4', 2, 1000, 0, 'entry4.jpg'),
      ('5', 2, 1001, 1, 'entry5.jpg'),
      ('6', 3, 1002, 0, 'entry6.jpg'),
      ('7', 3, 1003, 0, 'entry7.jpg');
    Code (sql):
    and

    select `entries`.`id`, `contests`.`prize_name`, `entries`.`outcome`, `entries`.`entry_image`
    from entries
    inner join contests on entries.contest_id = contests.id
    where user_id = 1000;
    
    select contests.id, contests.prize_name, contests.status, entries.user_id,
    count(entries.id) as counter,
    case when sum(entries.outcome) > 0 then 'winner' else '' end as outcome
    from entries
    inner join contests on entries.contest_id = contests.id
    group by contests.id, entries.user_id
    order by contests.status, contests.prize_name, entries.user_id;
    Code (sql):
     
    sarahk, Jan 20, 2020 IP