how to max(count(id))
Posted: Sun Sep 21, 2008 11:30 am
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
_______________________________
wedding gift Audi A4 Control Arm Bushings
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
_______________________________
wedding gift Audi A4 Control Arm Bushings