sql query combine two queries into one with empty rows

Question

This is my sql table structure:

Table1: details

    |--id--|--id_user--|--price--|
    |  1   |    1      |   10    |
    |  2   |    2      |   15    |
    |  3   |    1      |   25    |
    |  4   |    3      |   30    |
    |  5   |    3      |   7     |
    ------------------------------

Table2: users

    |--id--|--id_country--|
    |  1   |       1      |
    |  2   |       2      |
    |  3   |       0      |
    -----------------------

Table3: country

    |--id--|--country--|
    |  1   |  France   |
    |  2   |  Italy    |
    --------------------

What I need is to get the SUM of price by country:

SELECT c.country, SUM(d.price) AS price
    FROM details d
        INNER JOIN users u ON u.id = d.id_user
        INNER JOIN country c ON  c.id = u.id_country
    GROUP BY c.country
    ORDER BY c.country

I get this:

|--country--|--price--|
|  France   |   35    |
|  Italy    |   15    |
-----------------------

BUT I'd need to get this:

|--country--|--price--|
|  France   |   35    |
|  Italy    |   15    |
| Undefined |   37    |
-----------------------

where undefined would be if id_country=0. (I can't add to country table the id=0 or id=undefined, it will messed up other things). Right now I'm achieving this by two separate queries, the second one is:

SELECT SUM(d.price) as price
    FROM details d 
        INNER JOIN users u ON u.id = d.id_user AND u.id_country=0
    GROUP BY u.id_country

I'm thinking if... is it possible to do this in one query?

It is strange to see that you can use an id_country in your users table that doesn't exist. That shows that you don't use your database properly. You should have a foreign key constraint and make the column nullable if you want to allow for "undefined country". — Thorsten Kettner
– Thorsten Kettner, Commented Oct 6, 2016 at 7:59
@Thorsten-Kettner can you write referring to the above example how should I construct this properly? — felixRo
– felixRo, Commented Oct 6, 2016 at 8:10
You'd usually create the tables with foreign keys. So you create table Country first with id being the primary key, and then you create table users with users.id_country being a foreign key to country.id. But you can also add this later with alter table. Read here: mysqltutorial.org/mysql-foreign-key — Thorsten Kettner
– Thorsten Kettner, Commented Oct 6, 2016 at 8:23

Linkan · Accepted Answer · 2016-10-06 08:41:56Z

1

You need to use left join in this case:

SELECT c.country, SUM(d.price) AS price
    FROM details d
        LEFT JOIN users u ON u.id = d.id_user
        LEFT JOIN country c ON  c.id = u.id_country
    GROUP BY c.country
    ORDER BY c.country

If you use INNER JOIN, you will only get results that exists in both tables.

To replace NULL with Undefined use:

SELECT IFNULL(c.country,'Undefined') AS Country, SUM(d.price) AS price
    FROM details d
        LEFT JOIN users u ON u.id = d.id_user
        LEFT JOIN country c ON  c.id = u.id_country
    GROUP BY c.country
    ORDER BY c.country

One way to sort to get Undefined last is to add a Sortfield

SELECT A.Country,A.Price FROM (
    SELECT IFNULL(c.country,'Undefined') AS Country, SUM(d.price) AS price, IFNULL(c.Country,'ZZZZZZZZ') AS Sort
        FROM details d
            LEFT JOIN users u ON u.id = d.id_user
            LEFT JOIN country c ON  c.id = u.id_country
        GROUP BY c.country
) A
ORDER BY A.Sort

Edit: ORDER BY suggested in comments

SELECT IFNULL(c.country,'Undefined') AS Country, SUM(d.price) AS price
    FROM details d
        LEFT JOIN users u ON u.id = d.id_user
        LEFT JOIN country c ON  c.id = u.id_country
    GROUP BY c.country
    ORDER BY c.country IS NULL, c.country

edited Oct 6, 2016 at 8:41

answered Oct 6, 2016 at 7:31

Linkan

5791 gold badge7 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

felixRo Over a year ago

your right, is it possible to change the "null" to "undefined" or other word and to insert at the beginning or end of the result table?

Thorsten Kettner Over a year ago

To get nulls at the end use: ORDER BY c.country IS NULL, c.country.

felixRo Over a year ago

I like this middle answer starting with SELECT IFNULL but at the end use suggestion from @Thorsten-Kettner ORDER BY c.country IS NULL, c.country and works great! Thank you for answers.

Mr. Bhosale · Accepted Answer · 2016-10-06 08:27:41Z

0

Try below query.

        SELECT  
        CASE  
        WHEN c.country is NULL THEN 'Undefined'
        ELSE c.country
        END as country
        , SUM(d.price) AS price
        FROM  users u 
        left JOIN details d ON u.id = d.id_user
        left JOIN country c ON  c.id = u.id_country
        GROUP BY c.country
        ORDER BY c.country

For Demo :

SqlfiddlE Demo :

Please let us know if you have any que.

edited Oct 6, 2016 at 8:27

answered Oct 6, 2016 at 8:20

Mr. Bhosale

3,1161 gold badge19 silver badges37 bronze badges

Collectives™ on Stack Overflow

sql query combine two queries into one with empty rows

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related