Results 1 to 2 of 2

Thread: Report

  1. #1
    Join Date
    Sep 2009
    Posts
    34

    Unanswered: Report

    thanks all members for guidance

    Today my question is that i have one table named shop in which i have 2 fields one is city and the other one is status.

    in city field i have the following entries

    Brisbane
    Bundaberg
    Cairns
    Caloundra
    Charters Towers
    Gladstone

    and in the status field i have the following entries

    active
    defaulter
    close

    now i want a build report to display the following data

    example

    city____________active(1)________defaulter(2)_____ __closed(3)_______total(1+2+3)

    Brisbane_________10_______________10______________ _2______________22


    the above example shows that in Brisbane city 10 shops are active, 10 shops are defaulter, 2 shops are closed and total shows that 22 shops are located in Brisbane city.

    now plz help how to build this report

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    SELECT CITY,SUM(DECODE(STATUS,'active',1,0)) ACTIVE,
                SUM(DECODE(STATUS,'defaulter',1,0)) DEFAULTER,
                SUM(DECODE(STATUS,'CLOSE',1,0)) CLOSED,
                COUNT(*) TOTAL
    FROM SHOP
    GROUP BY CITY
    order by city;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •