mysql - Get Multiple COUNTS Multiple JOINS from Schedule table -


i have 3 tables:

test_member, test_member_addon & test_schedule.

  • a member can have 1 test_member.type (test_schedule.prod_type = 1)
  • a member can have multiple test_member_addon.type (test_schedule.prod_type = 2)

i trying return distinct counts each distinct test_schedule.mem_id, test_schedule.prod_code in schedule future dated , schedule.status = 1.

schedules should have correct amount of payments eg. 52 'weekly'. if correct & actual not match, can fix.

my query: not return correct values amount, period, correct or actual.

select distinct s.mem_id, case when s.prod_type = 1 round(m.next_amount,2) else round(ma.next_amount,2) end amount,  case when s.prod_type = 1 m.period else ma.period end period, case when s.prod_type = 1  ( case when m.period = 'weekly' 52  when m.period = 'fortnightly' 27 end ) else ( case when ma.period = 'weekly' 52  when ma.period = 'fortnightly' 27 end  ) end correct, count(*) actual,  s.prod_code,  s.prod_type `test_schedule` s, `test_member` m, `test_member_addon` ma  m.id = s.mem_id , ma.mem_id = m.id  , s.status = 1  group s.prod_code, s.mem_id  order s.mem_id, s.prod_type;

db build scripts:

create table `test_member` (   `id` int(11) not null auto_increment,   `next_amount` float default null,   `period` varchar(50) default null,   `prod_code` int(11) not null,   primary key (`id`) ) engine=innodb auto_increment=4 default charset=latin1;  insert `test_member` (`id`, `next_amount`, `period`, `prod_code`) values ('1','50','weekly',11), ('2','35','fortnightly',11);  create table `test_member_addon` (   `mem_addon_id` int(11) not null auto_increment,   `mem_id` int(11) default null,   `next_amount` float default null,   `period` varchar(50) default null,   `prod_code` int(11) not null,   primary key (`mem_addon_id`) ) engine=innodb auto_increment=6 default charset=latin1;  insert `test_member_addon` (`mem_addon_id`, `mem_id`, `next_amount`, `period`, `prod_code`) values ('1','1','25.55','weekly',22), ('2','1','15','fortnightly',33);  create table `test_schedule` (   `sched_id` int(11) not null auto_increment,   `mem_id` int(11) not null,   `prod_code` int(11) not null,   `prod_type` int(11) not null,   `status` smallint(4) not null,   `amount` float not null,   `sched_date` date not null,   primary key (`sched_id`) ) engine=innodb auto_increment=22 default charset=latin1;  insert `test_schedule` (`sched_id`, `mem_id`, `prod_code`, `prod_type`, `status`, `amount`, `sched_date`) values ('1','1','11','1','1','50','2014-03-21'), ('2','1','11','1','1','50','2014-03-28'), ('3','1','11','1','1','50','2014-04-04'), ('4','1','11','1','1','50','2014-04-11'), ('5','1','11','1','1','50','2014-04-18'), ('6','1','22','2','1','25.55','2014-03-21'), ('7','1','22','2','1','25.55','2014-03-28'), ('8','1','22','2','1','25.55','2014-04-04'), ('9','1','22','2','1','25.55','2014-04-11'), ('10','1','22','2','1','25.55','2014-04-18'), ('11','1','22','2','1','25.55','2014-04-25'), ('12','1','22','2','1','25.55','2014-05-02'), ('13','1','22','2','1','25.55','2014-05-09'), ('14','1','33','2','1','15','2014-03-21'), ('15','1','33','2','1','15','2014-04-04'), ('16','1','33','2','1','15','2014-04-18'), ('17','1','33','2','1','15','2014-05-02'), ('18','1','33','2','1','15','2014-05-16'), ('19','1','33','2','1','15','2014-05-30'), ('20','1','33','2','1','15','2014-06-13'), ('21','1','33','2','1','15','2014-06-27');  current return data: mem_id |    amount   |    period    | correct  |  actual  |prod_code | prod_type 1      |    50.00    |    weekly    |    52    |    10    |    11    |    1 1      |    25.55    |    weekly    |    52    |    16    |    22    |    2 1      |    25.55    |    weekly    |    52    |    16    |    33    |    2   correct data: mem_id |    amount   |    period    | correct  |  actual  |prod_code | prod_type 1      |    50.00    |    weekly    |    52    |    5     |    11    |    1 1      |    25.55    |    weekly    |    52    |    8     |    22    |    2 1      |    15       | fortnightly  |    27    |    8     |    33    |    2

try way

select mem_id, prod_code, prod_type, period,         round(max(amount), 2) amount,         case when period = 'weekly'  52 else 27 end correct,              count(*) actual   (   select s.*, m.period     test_schedule s join test_member m       on s.mem_id = m.id       , s.prod_code = m.prod_code      , s.prod_type = 1    s.status = 1    union    select s.*, a.period     test_schedule s join test_member_addon        on s.mem_id = a.mem_id       , s.prod_code = a.prod_code      , s.prod_type = 2    s.status = 1  ) q  group mem_id, prod_code, prod_type, period

output:

 | mem_id | prod_code | prod_type |      period | amount | correct | actual | |--------|-----------|-----------|-------------|--------|---------|--------| |      1 |        11 |         1 |      weekly |     50 |      52 |      5 | |      1 |        22 |         2 |      weekly |  25.55 |      52 |      8 | |      1 |        33 |         2 | fortnightly |     15 |      27 |      8 |

here sqlfiddle demo


Comments

Post a Comment

Popular posts from this blog

google app engine - 403 Forbidden POST - Flask WTForms -

i have flask application running on google app engine. trying submit form built using wtform , keep getting following error. 403 forbidden: don't have permission access requested resource. either read-protected or not readable server. location.html (part of code) <form method=post action="/home/location"> .... <button type="submit" class="btn btn-primary" value="submit"> directions </button> </form> main.py @app.route('/home/location', methods=['post', 'get']) def location(): form = cfcdirections.direction(request.form) print(request.method) if request.method == 'post' , form.validate(): print("after if") directions = cfcdirections() street_no = directions.no street = directions.street suburb = directions.suburb postcode = directions.postcode ...
Read more

Android layout hidden on keyboard show -

i have strange problem layout. when tap on edittext , keyboard shown edittext stay on bottom , when tap on to hide keyboard , keyboard hides , edittext go should when want input text. when tap again, keyboard shown , edittext goes down behind keyboard... <relativelayout android:layout_width="match_parent" android:layout_height="match_parent" > <scrollview android:layout_width="match_parent" android:layout_height="fill_parent" android:above="@+id/rl_commands"> <textview android:layout_width="match_parent" android:layout_height="wrap_content"/> </scrollview> <relativelayout android:layout_width="match_parent android:layout_height="24dp" android:id="@+id/rl_commands" android:alignparentbottom="true"> ...
Read more

Parse xml element into list in Python -

i've been trying days store list of coordinates list in python. i've tried minidom, xmlreader, , few others. i'm doing wrong, i'm on deadline , need help. <?xml version="1.0" encoding="utf-8"?> <kml xmlns="http://www.opengis.net/kml/2.2" xmlns:gx="http://www.google.com/kml/ext/2.2" xmlns:kml="http://www.opengis.net/kml/2.2" xmlns:atom="http://www.w3.org/2005/atom"> <document> <name>kmlfile</name> <style id="s_ylw-pushpin_hl"> <iconstyle> <scale>1.3</scale> <icon> <href>http://maps.google.com/mapfiles/kml/pushpin/ylw-pushpin.png</href> </icon> <hotspot x="20" y="2" xunits="pixels" yunits="pixels"/> </iconstyle> </style> <stylemap id="m_ylw-pushpin"> ...
Read more