Group records by paydate

[JDevOnline]

Hi Barand,

I hope you don't mind posting my requests in the same thread:

I need help  with mysql query to fetch records in from a single database table grouped by paydate. The structure of table is as follows:

id    int(11) - P,U,AI
sid    int(10) - Student ID from another table
shift    int(1) - values: 1 or 2
name    varchar(50)
vno    int(10) - voucher No. typically a 6 digit number
class    varchar(20)
mfee    varchar(6)     - Monthly fee
month    date
issuedate    date
lastdate    date
paydate    date

Each row in the table keeps the record of fee paid by a student for one month and every students has multiple rows.

What I want to achieve is to fetch records of single month grouped by paydate, example:

PayDate: 2020-04-01 & Month: 2020-04-01:
Name1 | Class1 | Shift1 | VNo 420112
Name2 | Class1 | Shift2 | VNo 42080
Name3 | Class3 | Shift2 | VNo 42063
.....
PayDate: 2020-04-02 & Month: 2020-04-01:
Name4 | Class1 | Shift1 | VNo 42066
Name5 | Class1 | Shift2 | VNo 42034
Name6 | Class3 | Shift2 | VNo 42099
.....

Edited April 22, 2020 by JDevOnline

[Barand]

You hope in vain.

What have you tried?

Edited April 22, 2020 by Barand

[JDevOnline]

Hi Barand,

I've tried the following query but it is showing only one row of data per paydate:

Select distinct paydate, name, class, shift, vno from mfee where month='2020-04-01' and paid 'Y'

 

[Barand]

Can't believe that query produces anything.

14 hours ago, JDevOnline said:

What I want to achieve is to fetch records of single month grouped by paydate

When you say "grouped by" do you mean "ordered by"? There is a huge difference in SQL between the two.

[JDevOnline]

Hi Barand,

In simple term, I want to fetch the details of payments, grouped by date, that is, on particular dates, as mentioned in my first post, thanks.

[Barand]

The clue was in "ORDER BY"