See number of different types of booking per organisation

[Adamhumbug]

Hi all,

I have a query that shows me the number of bookings that an organisation has made which works perfectly

select o_id as id, o_name as org, pass_641.media_attendee from prs_o
inner join prs_op on o_id = op_o_id
inner join (select pr_op_id, pr_ptd_id, count(*) as media_attendee from prs_pr where pr_ptd_id = 641 group by pr_o_id ) 
as pass_641 on op_id = pass_641.pr_op_id

The data that i get out looks like the below (i have left out the name col)

org       count

1           2

2          3

3          9

 

I want to add another count of other types of order to the query and tried the following

select o_id as id, o_name as org, pass_641.media_attendee, pass_1001.tv from prs_o
inner join prs_op on o_id = op_o_id
inner join (select pr_op_id, pr_ptd_id, count(*) as media_attendee from prs_pr where pr_ptd_id = 641 group by pr_o_id ) 
as pass_641 on op_id = pass_641.pr_op_id
inner join (select pr_op_id, pr_ip2ptd_id, count(*) as tv from prs_pr where pr_ip2ptd_id = 1001 group by pr_o_id ) 
as pass_1001 on op_id = pass_1001.pr_op_id

This only shows me organisations that have both of the order types rather than showing NULL if they have one and not the other - this is clearly to do with the inner joins.

I tried changing to left joins but this shows a row per order like the below

 

org       count       count

1          2                NULL

1          NULL         NULL

 

I would appreciate a point in the right direction with this.

To confirm if i run this with only one order type the individual queries run correctly.

 

[Barand]

Any chance of table structures and some test data? (Saves a job at this end.)

[Adamhumbug]

prs_o table: o_id, o_name

prs_op table: op_id, op_o_id - op_o_id joins to o_id from above

prs_pr table: pr_Id, pr_op_id, prs_ptd_id, pr_ip2ptd_id - pr_op_id joins to op_id from above

 

 

 

[Barand]

A dump of the structure and test data would have benn appreciated.

Now, having created the tables from the structures you provided and then having entered the data manually from that useless picture of the data, I now find that when I run your queries, some of the column names in your structures do not match those used in your queries. Aaaaargh!

I am rapidly losing patience with this problem. It may take longer than expected. Much longer

[Barand]

Sorry, but with those typos I don't know which ones are correct and which aren't. ( therefore which columns should really be used for the join betwen prs_op and prs_pr). All those op_id and o_id names are confusing. I don't know if you really mean o_id when you say op_id or vice versa.

[Adamhumbug]

prs_ptd_id is a typo - it should be pr_ptd_id

 

pr_op_id is the id of the person that made the order stored on the order table (prs_pr) and links to op_id in the person table (prs_op)

prs_ptd_id (is the order definition)

pr_o_id is the organisation the order is stored against in the order table (prs_pr) this links to o_id stored in the organiastion table (prs_o)

 

orders belong to a person and the person belongs to an organsation

Edited June 18, 2021 by Adamhumbug

[Barand]

1 hour ago, Adamhumbug said:

pr_o_id is the organisation the order is stored against in the order table (prs_pr) this links to o_id stored in the organiastion table (prs_o)

That is precisely the problem I hve been having - there is no "pr_o_id" in the structure you gave me ...

4 hours ago, Adamhumbug said:

prs_pr table: pr_Id, pr_op_id, prs_ptd_id, pr_ip2ptd_id - pr_op_id joins to op_id from above

 

[Barand]

I'lll state my assumptions

1 ) The data looks like this

prs_o                       prs_op                      prs_pr
+------+-----------+        +-------+---------+         +-------+----------+------------+--------------+
| o_id | o_name    |        | op_id | op_o_id |         | pr_Id | pr_op_id | pr_ptd_id  | pr_ip2ptd_id |
+------+-----------+        +-------+---------+         +-------+----------+------------+--------------+
|    1 | Company A |        |     1 |       1 |         |     1 |        1 |        641 |         NULL |
|    2 | Company B |        |     2 |       1 |         |     2 |        2 |        641 |         NULL |
|    3 | Company C |        |     3 |       1 |         |     3 |        3 |       NULL |         1101 |
+------+-----------+        |     4 |       1 |         |     4 |        4 |       NULL |         1101 |
                            |     5 |       2 |         |     5 |        5 |       NULL |         1101 |
                            |     6 |       2 |         |     6 |        6 |       NULL |         1101 |
                            |     7 |       2 |         |     7 |        7 |       NULL |         1101 |
                            |     8 |       3 |         |     8 |        8 |       NULL |         1101 |
                            |     9 |       3 |         |     9 |        9 |       NULL |         1101 |
                            |    10 |       3 |         |    10 |       10 |       NULL |         1101 |
                            |    11 |       3 |         |    11 |       11 |       NULL |         1101 |
                            |    12 |       3 |         |    12 |       12 |        641 |         NULL |
                            |    13 |       3 |         |    13 |       13 |        641 |         NULL |
                            +-------+---------+         +-------+----------+------------+--------------+
                            

2 ) The table and join structure looks like this

+--------------+           +--------------+              +--------------+
| prs_o        |           | prs_op       |              | prs_pr       |
+--------------+           +--------------+              +--------------+
| o_id         |-----\     | op_id        |------\       | pr_id        |
| o_name       |      \---<| op_o_id      |       \-----<| pr_op_id     |
+--------------+           +--------------+              | pr_ptd_id    |
                                                         | pr_ip2ptdid  |
                                                         +--------------+

Query

SELECT o_id
     , o_name
     , SUM(pr_ptd_id = 641) as Tot641
     , SUM(pr_ip2ptd_id = 1101) as Tot1101
FROM prs_pr pr
     JOIN prs_op op ON pr.pr_op_id = op.op_id
     JOIN prs_o o ON op.op_o_id = o.o_id
GROUP BY o.o_id;

Results

+------+-----------+--------+---------+
| o_id | o_name    | Tot641 | Tot1101 |
+------+-----------+--------+---------+
| 1    | Company A | 2      | 2       |
| 2    | Company B |        | 3       |
| 3    | Company C | 2      | 4       |
+------+-----------+--------+---------+

 

[Adamhumbug]

Thanks so much and i appreciate you dealing with my rubbish post.