CODEWITHSUNDEEP
phpmysql
Abysinian
Abysinian

Reputation: 55

PHP/MySQL - Count number of sales made and display that number and who sold them

I'm working on a PHP application for University which pretends I buy and resell a specific item to/from countries. I have a table which contains transaction IDs and the names of the countries that made them.

I've been trying to count up the number of times a specific country's name appears in the database table then print out a table on the webpage showing each countries name and the number of times they appear in the table like below, but I can't find any examples of this kind of thing anywhere, they're always too different to be of much help. I would also like to order the table by max to min count and also be able to limit the number of countries shown using radio buttons (which I've already got working).

countryName | numberOfSales
Belize      |       10
Brazil      |        6
Cameroon    |       64
Colombia    |       23
Costa Rica  |       47

Upvotes: 0

Views: 2121

Answers (2)

AnotherGuy
AnotherGuy

Reputation: 615

If you have two tables in your database (MySql in my case), one where you store the different countries and one for the sales/orders.

Table countries:

id      INT
country VARHCAR(255)

Table: sales:

id         INT
country_id INT
title      VARCHAR(255)

Here there is a 1:many relationship between the two tables. One country can have many sales, but a sale can only belong to one country.

You can then fetch the number of sales for specific country by running the following SQL query. If you want to find the number of sales for Denmark you could do:

SELECT countries.country, COUNT(*) AS numberOfSales 
FROM countries 
    JOIN sales 
    ON sales.country_id = countries.id 
WHERE countries.country = 'denmark';

If you want to see the total number of sales for each country you can use the following SQL query:

SELECT countries.country, COUNT(*) AS numberOfSales 
FROM countries 
    JOIN sales 
    ON sales.country_id = countries.id
GROUP BY countries.country;

Here the GROUP BY clause will fetch the result for each of the countries in your countries table. You can add LIMIT clause if necessary.

I hope this doesn't complicate things too much. You will thank me later by using two tables (I do not know if you already do. If you do, NICE).

Related to counting identical rows in MySql: Count number of identical rows in MySQL with PHP

Best regards.

Upvotes: 1

slapyo
slapyo

Reputation: 2991

You need to select the country and count, then group by the country. If you want to order it you need to add that on as well.

SELECT
    countryName,
    count(*) as numberOfSales 
FROM
    sales
GROUP BY
    countryName
ORDER BY
    numberOfSales DESC

This query will have 2 columns in the results. The country names and the number of times they appear according to the grouping. We are grouping all of the records by the country name column. In the query, we name the column numberOfSales, so we can use that when ordering it so that the countries will be in order from max to min in numberOfSales.

Upvotes: 2

Related Questions