Mysql joining two tables and counting total records based on primary key

Question

I have two tables vendor and medicines on mysql database now i need to count total medicines transacted based on vendor ID with a condition status is equal to paid, in a single query and show it to my users

My vendor table looks like this

| ID |   vendor_name    |
+----+------------------+
|  1 | Marakel Medicine |
|  2 | Ignacio Pharmacy |
|  3 | Rico Medicines   |
|  4 | Marco Pharmacy   |
+----+------------------+

My Medicines table looks like this.

| ID | vendor_id | medicine_name  | Status  |
+----+-----------+----------------+---------+
|  1 |         1 | paracetamol #1 | paid    |
|  2 |         1 | paracetamol #1 | paid    |
|  3 |         2 | paracetamol #1 | pending |
|  4 |         2 | paracetamol #1 | paid    |
|  5 |         4 | paracetamol #1 | paid    |
+----+-----------+----------------+---------+

My desire output would be like this

+----+------------------+-----------+
| ID |   vendor_name    | Total Med |
+----+------------------+-----------+
|  1 | Marakel Medicine |         2 |
|  2 | Ignacio Pharmacy |         1 |
|  3 | Rico Medicines   |         0 |
|  4 | Marco Pharmacy   |         1 |
+----+------------------+-----------+

So far my code is below i was able to output per vendor i just don't have an idea how to count them based on there status, any suggestion would be great!

SELECT * FROM vendors LEFT JOIN medicine ON vendor.ID = medicines.vendor_id

Tharif · Accepted Answer · 2017-10-14T05:31:41.547

2

Join Operation on tables based on status[paid or pending] & vendor name :

Proposed Query :

SELECT        vendor.ID, vendor.vendor_name, 
COUNT((CASE WHEN [status] <> 'pending' THEN 1 END)) AS 'Total Med'

FROM            medicines RIGHT OUTER JOIN
                vendor ON medicines.vendor_id = vendor.ID

GROUP BY vendor.vendor_name, vendor.ID

Note: This query has been worked out in MSSQL, you can refer the logic and try for MySQL.

Corresponding MySQL Syntax:

SELECT vendor.ID, vendor.vendor_name, 
COUNT((CASE WHEN status <> 'pending' THEN 1 END)) AS 'Total Med' 
FROM medicines 
RIGHT OUTER JOIN vendor ON 
medicines.vendor_id = vendor.ID GROUP BY vendor.vendor_name, vendor.ID

edited Oct 14 '17 at 05:31

answered Oct 14 '17 at 04:33

Tharif

13,794
9
55
77

This worked great, thanks! is there a way to optimize the query is slow since this will be a big database in the future. – KaoriYui Oct 14 '17 at 05:51
1

happy to see that query saved your time. before optimising query i would suggest you to understand the logic and try some similar examples. Regarding optimising query, several steps can be done like indexing or working out parts where atmost delay occur, some SO posts for your help : https://stackoverflow.com/questions/3801188/sql-query-optimization ; https://stackoverflow.com/questions/18152633/sql-server-query-optimization – Tharif Oct 14 '17 at 06:00
Note that nobody ever uses RIGHT JOIN – Strawberry Oct 14 '17 at 09:20
@Strawberry any wrong in query ? please share your thoughts ..Thanks – Tharif Oct 14 '17 at 11:06
It would be more usual to write that something like this: SELECT v.ID , v.vendor_name , SUM(status <> 'pending') 'Total Med' FROM vendor v LEFT JOIN medicines m ON m.vendor_id = v.ID GROUP BY v.ID , v.vendor_name; Also, there's a functional dependency on vendor_name, so its inclusion in the GROUP BY isn't strictly necessary - but good practice all the same. – Strawberry Oct 14 '17 at 11:13
@Strawberry, am just a beginner in sql query building.. will keep your note in mind.. Thanks – Tharif Oct 14 '17 at 11:21

score 1 · Answer 2 · answered Oct 14 '17 at 03:42

1

Use GROUP BY and COUNT

SELECT
    v.ID,
    v.vendor_name,
    COUNT(m.vendor_id) AS 'Total Med'
FROM
    vendors v
LEFT JOIN medicine m ON v.ID = m.vendor_id
GROUP BY
    v.ID

answered Oct 14 '17 at 03:42

kip

1,120
7
11

query will not fetch output as needed by OP – Tharif Oct 14 '17 at 04:40

score 1 · Answer 3 · edited Oct 14 '17 at 05:06

1

you should try this

SELECT v.ID,v.vendor_name,COUNT(m.ID) as `Total`
FROM vendor v LEFT JOIN medicines m ON v.ID=m.vendor_id
GROUP BY m.ID

edited Oct 14 '17 at 05:06

GYaN

2,327
4
19
39

answered Oct 14 '17 at 04:46

Nazish Fraz

94
1
9

Its giving me the `unknow column vendor.ID` error still working on it and trying to get it to work. – KaoriYui Oct 14 '17 at 05:13
may be your column name for that vendor table is in small, please check that column name, – Nazish Fraz Oct 15 '17 at 08:40

azbatuk · Answer 4 · 2017-10-14T05:01:44.647

1

MYSQL version of @tharif's MSSQL solution.

SELECT
    v.ID,
    v.vendor_name,
    COUNT((CASE WHEN m.`status` <> 'pending' THEN 1 END)) AS 'Total Med'
FROM
    vendor v
LEFT JOIN medicines m ON v.ID = m.vendor_id
GROUP BY v.ID

It is a good idea to read on group by and count if you are new to these.

edited Oct 14 '17 at 05:01

answered Oct 14 '17 at 04:53

azbatuk

273
1
6

I AM A Hacker · Answer 5 · 2017-10-14T07:03:53.560

0

Use Group By its an sql function and also use count its also an sql built in function..some similar shown below.

    SELECT vendors.id,vendors.vendor_name,COUNT(medicine.vendor_id) AS 'Total Transaction'
    FROM vendors LEFT JOIN medicine ON vendors.id = medicine.vendor_id 
    GROUP BY vendors.id

edited Oct 14 '17 at 07:03

answered Oct 14 '17 at 04:19

I AM A Hacker

113
1
1
10

its was just for the sake of example the user who posted the question indeed know what baically id or ID is – I AM A Hacker Oct 14 '17 at 04:59
is it ok now just added the table names for your satisfaction – I AM A Hacker Oct 14 '17 at 07:04

Mysql joining two tables and counting total records based on primary key

5 Answers5