Counting ITEMS

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • jas2803
    New Member
    • Oct 2007
    • 30

    #1

    Counting ITEMS

    Hello,
    I have a table:
    [ID][DATE][Choice][Quantity]

    The person gets to come back as often as possible and choose between 3 different items, A, B C, for the [choice] column. They can take as many as they want, [Quantity] column.
    I want a query that groups by id, giving me a count of A., Count of B and Count of C choices. Then I want to have a sum of each quantity.
    I am not able to figure out how to get a count of each choice type per ID.
    Any ideas?
    Please and Thanks
  • mwasif
    Recognized Expert Contributor
    • Jul 2006
    • 802

    #2
    You can try
    [CODE=mysql]SELECT ID, SUM(IF(Choice=' A',Quantity,0)) AS A_Quantity,
    SUM(IF(Choice=' B',Quantity,0)) AS B_Quantity,
    SUM(IF(Choice=' C',Quantity,0)) AS C_Quantity,
    SUM(Quantity) AS total_Quantity
    FROM table_name
    GROUP BY ID[/CODE]

    Comment

    • jas2803
      New Member
      • Oct 2007
      • 30

      #3
      I will give this a go, thanks very much for the response.
      Originally posted by mwasif
      You can try
      [CODE=mysql]SELECT ID, SUM(IF(Choice=' A',Quantity,0)) AS A_Quantity,
      SUM(IF(Choice=' B',Quantity,0)) AS B_Quantity,
      SUM(IF(Choice=' C',Quantity,0)) AS C_Quantity,
      SUM(Quantity) AS total_Quantity
      FROM table_name
      GROUP BY ID[/CODE]

      Comment

      • jas2803
        New Member
        • Oct 2007
        • 30

        #4
        Perfect....exac tly what I needed....works like a charm.
        Originally posted by mwasif
        You can try
        [CODE=mysql]SELECT ID, SUM(IF(Choice=' A',Quantity,0)) AS A_Quantity,
        SUM(IF(Choice=' B',Quantity,0)) AS B_Quantity,
        SUM(IF(Choice=' C',Quantity,0)) AS C_Quantity,
        SUM(Quantity) AS total_Quantity
        FROM table_name
        GROUP BY ID[/CODE]

        Comment

        Working...