Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008
    Posts
    1

    Unanswered: problem with max(count(id))

    i have table like this

    id tanggal airline h_jual
    1 2008-08-03 2 458888
    2 2008-08-05 2 500000
    3 2008-08-05 1 500000
    4 2008-08-05 3 709999
    5 2008-05-13 1 500000
    6 2008-07-13 3 709999
    7 2008-06-13 3 500000
    8 2008-01-13 1 458888
    9 2008-01-13 1 500000
    10 2008-02-13 3 458888
    11 2008-03-13 3 458888
    12 2008-04-13 2 458888
    13 2008-09-14 4 458888
    14 2008-09-14 2 1200000
    15 2008-09-14 4 1200000
    16 2008-01-14 2 709999
    17 2008-01-17 1 1200000
    18 2008-01-17 2 1200000
    19 2008-01-17 3 1200000
    20 2008-01-17 4 1200000
    21 2008-02-17 1 1200000

    i wrote like this
    Code:

    SELECT distinct year(tanggal) as year, month(tanggal) as month, count(id) as jml_transaksi, airline as airline FROM `table` where year(tanggal)='2008' group by year(tanggal), month(tanggal), airline order by tanggal asc


    i got:

    year |month |jml_transaksi |airline
    2008 1 3 1
    2008 1 2 2
    2008 1 1 3
    2008 1 1 4
    2008 2 2 3
    2008 2 1 1
    2008 2 1 2
    2008 2 1 4
    2008 3 2 3
    2008 3 1 4
    2008 3 1 1
    2008 3 1 2
    2008 4 2 2
    2008 4 1 1
    2008 4 2 3
    2008 4 1 4
    2008 5 2 1
    2008 5 1 2
    2008 5 1 3
    2008 5 1 4
    2008 6 2 3
    2008 6 1 4
    2008 6 1 1
    2008 6 1 2
    2008 7 2 3
    2008 7 1 1
    2008 7 1 2
    2008 7 1 4
    2008 8 3 2
    2008 8 2 3

    i want got max in count(id) out? like in month 1 2008 i got 3 for maximum number even for 1,2,3,4 airline, how to get this result? any idea?
    i try this
    Code:

    SELECT distinct year(tanggal) as year, month(tanggal) as month, count(id) as jml_transaksi, airline as airline, max(count(id)) FROM `keuangan` where year(tanggal)='2008' group by year(tanggal), month(tanggal), airline order by tanggal asc

    but error!

    Thanks

    PS: sorry double post in subthreath others(database)

  2. #2
    Join Date
    Jun 2008
    Posts
    5
    i feel that, when you use the first code to get the table as the second you put out, you just use the max(id) in your code, this will give you the result you want

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT mm
         , MAX(jml_transaksi) AS max_jml_transaksi
      FROM ( SELECT MONTH(tanggal) AS mm
                  , COUNT(id) AS jml_transaksi
               FROM `table` 
              WHERE year(tanggal) = 2008 
             GROUP 
                 BY MONTH(tanggal) ) AS dt
    GROUP
        BY mm
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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