Web Analytics Made Easy -
StatCounter A little help? Hard to explain. - CodingForum

Announcement

Collapse
No announcement yet.

A little help? Hard to explain.

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • A little help? Hard to explain.

    Hi,

    I am trying to put an Invoices table together that is structured with the following columns:

    Month | City | Venue | Product1Quantity | Product2Quantity | Product3Quantity | Product4Quantity

    But I can only work out how to do this:

    Month | City | Venue | ProductName | Quantity

    ...and multiple rows to show quantities for each individual product. The SQL is:

    select (date_format( invoice_date,'%b')) as Month, city_name as City, venue_name as Venue, product_name AS Product, SUM(quantity) as Quantity
    from invoices
    inner join invoice_row on invoice_row.invoice_id=invoices.invoice_id
    inner join product on product.product_id=invoice_row.product_id
    inner join venue on venue.venue_id=invoices.venue_id
    inner join city on city.city_id = venue.venue_city
    where invoices.client_id =8 AND quantity != 0
    Group by City, Venue, Month, Product
    order by invoice_date, City

    Is there any way of taking the product names and making them columns so that each invoice can appear on one line with quantities under each product?

    Your help would be greatly appreciated.
    Chris Holbrook
    Freelance Designer and Musician
    Freelance Web Designer and Musician: Bristol, UK
    Visit my site: http://www.chrisholbrook.com

  • #2
    It sounds like you are talking about formatting the data after you've selected from the database, which is a language-specific issue. What language are you using?

    Comment


    • #3
      Oh yes.

      Its mysql. I am using Navicat to input the query, and export the results as an Excel spreadsheet (but that shouldn't make a difference should it?)

      Thanks a lot

      Chris
      Chris Holbrook
      Freelance Designer and Musician
      Freelance Web Designer and Musician: Bristol, UK
      Visit my site: http://www.chrisholbrook.com

      Comment


      • #4
        you would do this with your software used for formatting your output like php or coldfusion, not within mysql itself.

        Comment


        • #5
          Originally posted by latemodern View Post
          Its mysql. I am using Navicat to input the query, and export the results as an Excel spreadsheet (but that shouldn't make a difference should it?)
          why don't you create a pivot table in excel?
          Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

          Comment

          Working...
          X