SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: select help

  1. #1
    ********* Wizard silver trophy Cam's Avatar
    Join Date
    Aug 2002
    Location
    Burpengary, Australia
    Posts
    4,495
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    select help

    I have this table
    Code:
    +--------+-------------+------+-----+---------+----------------+
    | Field  | Type        | Null | Key | Default | Extra          |
    +--------+-------------+------+-----+---------+----------------+
    | id     | smallint(4) |      | PRI | NULL    | auto_increment |
    | type   | varchar(20) |      |     |         |                |
    | title  | varchar(70) |      |     |         |                |
    | method | text        |      |     |         |                |
    | switch | tinyint(1)  | YES  |     | 0       |                |
    +--------+-------------+------+-----+---------+----------------+
    and I want to select all the individual types and the number of records with that type where switch = 1. Sample rows could be this
    Code:
    1	MyCat		Script		More text	1
    2	OtherCat	Whois		Content		1
    3	MyCat		DNS		Doobie		1
    4	OtherCat	Section 508	Guidelines	0
    and from that I want
    Code:
    MyCat		2
    OtherCat	1
    Now I've done enough asking other people to do my work for me I've had a few shots myself but they returned huge numbers which were obviously incorrect.
    Code:
    select distinct
    	g.type
    	, count(g2.*) as num
    from
    	games as g
    	, games as g2
    where
    	g2.switch = 1
    	and g.type = g2.type
    Yeah, so obviously I'm not too good at harder kins of queries

    Thanks for any help

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select type
         , count(*) as num
      from games
     where switch = 1  
    group
        by type
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are making things wayyy too complicated:

    SELECT `type`, count(`type`) as total
    from games
    where switch=1
    group by `type`;

  4. #4
    ********* Wizard silver trophy Cam's Avatar
    Join Date
    Aug 2002
    Location
    Burpengary, Australia
    Posts
    4,495
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    Seems that I am Thanks heaps guys, not sure why I tried to do it that way but in my defense I was really tired


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
  •