0

I want to show my data in this format, data view client, And my Database structure is in this format, Sql View

EDITED I want to show my data in this format,

Name (Father name)  Jan (PKR)   Feb (PKR)   Mar (PKR)   Apr (PKR)
castiel(siddiqui)   20110                                           
Gull(yasal)         20010

And my DB structure is

c_id    name   salary       ds_id   amount      date_time
3       abc     20000       1       20010       2023-01-07 
4       abc     20000       2       20010       2023-01-07 
5       abc     20000       3       20010       2023-01-07 
6       abc     20000       4       20010       2023-01-07
6       abc     20000       5       20011       2023-02-07

I created this sql query without join to just check if I can, but no joy

SELECT courier.name
,MAX(IF(DATE_FORMAT(dis_salary.date_time,'%Y-m')='2023', dis_salary.amount,NULL)) as salary
FROM dis_salary
JOIN courier ON courier.c_id=dis_salary.courier_id
GROUP BY courier.c_id

I don't want to show max or sum, and all the dates are variable, so cant hardcode

I want to show each salary given to courier w.r.t month

Also the project is in PHP CodeIgniter, if that helps

output i want

name month1 month2
abc 20010 20011
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
CaStIeL SiDdIqUi
  • 3
  • 2
  • You should be selecting and grouping by courier and aggregating by month. Also please add sample data as text which we can use as opposed to images which we can't. – P.Salmon Jan 09 '23 at 07:32
  • Please review now, edited – CaStIeL SiDdIqUi Jan 09 '23 at 08:17
  • Since you have to group by to bring the different values into a single row per courier, you have to use an aggregate function. However, if a courier has only one salary per month, then max, min, avg, sum will all return the same amount. If a courier can have multiple salaries in a month that would give you a problem, but your question has not defined how to handle such situations, so we cannot give you a definite answer for that – Shadow Jan 09 '23 at 08:30
  • Salaries are only given once per month, also the question is marked as duplicate so, let me check with the duplicated question and get back to you here – CaStIeL SiDdIqUi Jan 09 '23 at 09:06
  • @shadow I've change the code, can you please guide me how to, i want data in months format and its only showing 1 salary of the year, I'll be setting only year dynamically to fetch all salaries per month in a year – CaStIeL SiDdIqUi Jan 09 '23 at 11:54
  • You have put in one conditional check only, so you get one result. Your code does not follow the logic described in the accepted answer. – Shadow Jan 09 '23 at 12:19

0 Answers0