Jump to content

See number of different types of booking per organisation


Adamhumbug
 Share

Go to solution Solved by Barand,

Recommended Posts

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.

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

image.png.a3760ac3474a4602cc8e48b94709451b.png

Link to comment
Share on other sites

Posted (edited)

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 by Adamhumbug
Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

  • Solution

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       |
+------+-----------+--------+---------+

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.