Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2007
    Posts
    26
    Rep Power
    0

    GROUP BY or COUNT help


    Hello I have a database table like the following:

    CREATE TABLE `contracts` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `user_name` varchar(25) NOT NULL,
    `project_name` varchar(255) NOT NULL,
    `contract_type_1` varchar(255) default NULL,
    `contract_type_2` varchar(255) default NULL,
    `contract_type_3` varchar(255) default NULL,
    `bids` int(10) unsigned NOT NULL default '0',
    `average` int(10) unsigned NOT NULL,
    `status` tinyint(1) unsigned default '0',
    `started` int(10) unsigned NOT NULL,
    `remaining` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

    If one row in this database had "PHP" under contract_type_1 and a second row had "PHP" under contract_type_2 and a third row entry had "PHP" under contract_type_3 my question is how do I make MySQL look for PHP in this case and return that PHP is found in all 3 rows/columns even though it is different columns?

    so in the end I could use this information to show
    PHP (3)

    here is some sample data

    INSERT INTO `contracts` VALUES (1,'bkerr8','Joomla template setup','Joomla','','',3,75,1,1261752672,1262460454),(2,'bkerr8','PHP bidding system for social media website','PHP','Joomla','CSS',3,350,1,1261752672,1262408400),(3,'bkerr8','design our web site for us','Web Design','PHP','Ruby/Ruby on Rails',44,750,1,1261977194,1262494800),(4,'bkerr8','Proofread articles from home earn extra money part-time','Proofreading','','',37,75,1,1261977194,1262494835),(5,'bkerr8','Flash banner for web site','Flash','Actionscript','Photoshop',16,285,1,1262148749,1262588400),(6,'bkerr8','Joomla SEO module setup','Joomla','SEO','',11,125,1,1262148749,1262588400),(7,'bkerr8','Microsoft .NET programmer needed for fulltime project','.NET','','',4,485,3,1261642644,1262235600),(8,'bkerr8','SEO optimization pro to help boost our website traffic on Google','SEO','','',22,280,1,1261642675,1262322000),(9,'bkerr8','graphic designer photoshop skills','Photoshop','','',17,350,3,1261642702,1262408400),(10,'bkerr8','MSSQL database analyst needed ASAP','MSSQL','','',47,450,1,1261642702,1262408000);
    Last edited by bkerr8; January 1st, 2010 at 08:13 AM. Reason: sample data
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Code:
    SELECT 'PHP'
         , COUNT(*)
      FROM contracts
     WHERE 'PHP' IN (contract_type_1,contract_type_2,contract_type_3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2007
    Posts
    26
    Rep Power
    0
    Originally Posted by r937
    Code:
    SELECT 'PHP'
         , COUNT(*)
      FROM contracts
     WHERE 'PHP' IN (contract_type_1,contract_type_2,contract_type_3
    cool that worked, i forgot to say i want to count how many times for the other skills like Joomla also not just PHP it was an example to help with the select, i need a count of all skills

    so I could get for example a count of each skill
    PHP(3)
    Joomla(2)

    etc etc for each skill in the table
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,803
    Rep Power
    353
    Originally Posted by bkerr8
    i forgot to say i want to count how many times for the other skills like Joomla also not just PHP it was an example to help with the select, i need a count of all skills

    so I could get for example a count of each skill
    PHP(3)
    Joomla(2)
    You should re-think your design.
    In order to be able to answer questions like that you need to move the skills part out of your contracts table.
    Code:
    CREATE TABLE contract_type 
    (
       contract_id int unsigned NOT NULL,
       contract_type varchar(255) NOT NULL, 
       PRIMARY KEY (contract_id, contract_type),
       FOREIGN KEY (contract_id) REFERENCES contracts (id)
    )
    and remove the non-normalized columns contract_type_1, contract_type_2 and contract_type_3
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    with your existing table design (which should be changed), you would have to "normalize on da fly"
    Code:
    SELECT contract_type
         , COUNT(*)
      FROM ( SELECT contract_type_1 AS contract_type
               FROM contracts
             UNION ALL
             SELECT contract_type_2
               FROM contracts
             UNION ALL
             SELECT contract_type_3
               FROM contracts ) AS normalized_on_da_fly
    GROUP
        BY contract_type
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2007
    Posts
    26
    Rep Power
    0
    Originally Posted by shammat
    You should re-think your design.
    In order to be able to answer questions like that you need to move the skills part out of your contracts table.
    Code:
    CREATE TABLE contract_type 
    (
       contract_id int unsigned NOT NULL,
       contract_type varchar(255) NOT NULL, 
       PRIMARY KEY (contract_id, contract_type),
       FOREIGN KEY (contract_id) REFERENCES contracts (id)
    )
    and remove the non-normalized columns contract_type_1, contract_type_2 and contract_type_3
    I have been reading off Google for hours and I agree with both of you regarding table design. I am just not sure how to structure it to support more than one contract type/skill using your table design, what the select on your table would look like etc. to support multiple skills

    thanks for your help guys, really appreciated
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by bkerr8
    ... what the select on your table would look like etc. to support multiple skills
    it would look like this --
    Code:
    SELECT contract_type 
         , COUNT(*)
      FROM contract_types
    GROUP
        BY contract_type
    (note slight change to shammat's table name)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2007
    Posts
    26
    Rep Power
    0
    Originally Posted by r937
    it would look like this --
    Code:
    SELECT contract_type 
         , COUNT(*)
      FROM contract_types
    GROUP
        BY contract_type
    (note slight change to shammat's table name)
    in my original table "id" was going to be the contract number and it auto increments, with this new table adding a row for each skill would give it a new contract number if that makes sense which is no good, this is why i had contract_type_1,contract_type_2,contract_type_3 all on 1 row, to keep the same contract number

    what i dont understand i guess is how to insert multiple skills for the same contract number since auto-increment gives it a new contract number everytime, i want mysql to generate the contract numbers
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,803
    Rep Power
    353
    Originally Posted by bkerr8
    iwhat i dont understand i guess is how to insert multiple skills for the same contract number since auto-increment gives it a new contract number everytime, i want mysql to generate the contract numbers
    You need to first insert a new contract, then you get back the generated ID.
    With that ID you insert into the contract_types table.
    Asume you have a contract with ID=42, then you'll add the types PHP, Jomlaa and Java to the database like this:
    Code:
    INSERT INTO contract_types (contract_id, contract_type)
    VALUES
    (42, 'PHP'),
    (42, 'Joomla'),
    (42, 'Java');
    If the set of contract types is limited (just a fixed number to "select" from), you might even go a stop further and store the types in a separate table (e.g. with an ID column) and then only store contract_id and contract_type_id in the table contract_types
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2007
    Posts
    26
    Rep Power
    0

    Talking


    Originally Posted by shammat
    You need to first insert a new contract, then you get back the generated ID.
    With that ID you insert into the contract_types table.
    Asume you have a contract with ID=42, then you'll add the types PHP, Jomlaa and Java to the database like this:
    Code:
    INSERT INTO contract_types (contract_id, contract_type)
    VALUES
    (42, 'PHP'),
    (42, 'Joomla'),
    (42, 'Java');
    If the set of contract types is limited (just a fixed number to "select" from), you might even go a stop further and store the types in a separate table (e.g. with an ID column) and then only store contract_id and contract_type_id in the table contract_types
    thanks for your help appreciate it
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by bkerr8
    with this new table adding a row for each skill would give it a new contract number if that makes sense which is no good
    it makes sense only if you assume that every table has to have an auto_increment

    this one doesn't

    Comments on this post

    • shammat agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2007
    Posts
    26
    Rep Power
    0
    Originally Posted by r937
    it makes sense only if you assume that every table has to have an auto_increment

    this one doesn't
    ok i re-made the tables, they now look like so

    CREATE TABLE IF NOT EXISTS `categories` (
    `contract_number` int(10) NOT NULL,
    `skills` varchar(50) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    CREATE TABLE IF NOT EXISTS `contracts` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_name` varchar(25) NOT NULL,
    `bids` smallint(5) unsigned NOT NULL DEFAULT '0',
    `average` smallint(5) unsigned NOT NULL DEFAULT '0',
    `status` tinyint(1) unsigned DEFAULT '0',
    `started` int(10) unsigned NOT NULL,
    `remaining` int(10) unsigned NOT NULL,
    `contract_name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=333 ;

    CREATE TABLE IF NOT EXISTS `skills` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `section` tinyint(1) unsigned DEFAULT NULL,
    `skill` varchar(50) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=314 ;

    I can get the info i need for the skills quantities no problem now with

    $sql = mysql_query("SELECT section,skill,COUNT(*) AS quantity FROM categories JOIN skills ON categories.skills = skills.id GROUP BY skill ORDER BY skill ASC") or die ("3");

    what i need help with is when i pull a record from contracts i need it to list all skills associated with the contract number

    $sql = mysql_query("SELECT * FROM contracts JOIN categories ON contracts.id = categories.contract_number") or die ("3");

    doing above with pull contract info but with just a skill number right now, i need all skills that belong to this contract listed like
    PHP, MySQL, Joomla

    while($row = mysql_fetch_array($sql)) {
    echo "<tr><td>" .$row['contract_name']. "</td><td>" .$row['bids']. "</td><td>" .$row['average']. "</td><td>" .$row['skills']. "</td><td>" .$row['started']. "</td><td>" .$row['remaining']. "</td></tr>";
    }
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    whoa, whoa, you're going too fast

    what happened to the contract_types table?

    what's a contract number for?

    why is categories.skills a VARCHAR?

    what is the skills table for?

    what are sections?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,803
    Rep Power
    353
    and why are you mixing InnoDB and MyISAM tables?

    and why don't you define proper foreign keys between the tables?
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2007
    Posts
    26
    Rep Power
    0
    Originally Posted by r937
    whoa, whoa, you're going too fast

    what happened to the contract_types table?

    what's a contract number for?

    why is categories.skills a VARCHAR?

    what is the skills table for?

    what are sections?
    i changed contract_types to categories
    contract_number is so i can reference skills from the contracts table which is id
    categories.skills is PHP, MySQL etc thats why i made it VARCHAR
    skills table holds all the skills like PHP, MySQL etc..
    sections dont even factor in to this problem, they are for sorting into sections 1-9 on the page thats it

    sorry guys iam trying to get these tables right, bear with me

    when i select from contracts, they skills comes back as a number not the name, only problem iam having now

    as for mixing dbases, thats what phpmyadmin chose, thx for your help appreciate it
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo