how to max(count(id))

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
susapra
Forum Newbie
Posts: 2
Joined: Sun Sep 21, 2008 11:22 am

how to max(count(id))

Post by susapra »

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
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: how to max(count(id))

Post by josh »

youre using an aggregrate function on another aggregrate function, aggregrate functions by defintion take aggregrate data and return scalar data.

in other words take out count()
Post Reply