CODEWITHSUNDEEP
sql
ven
ven

Reputation: 195

How to use SQL SELECT as field name

I'm trying to use select as column name to store the get total member, But I didn't succeed to get the solution for the right sql statement to use. Here is my database table structure and sql that I have used.

Sql

    SELECT 
        name, 
        SELECT
               (
                   (COALESCE(SUM(adult),0) + COALESCE(SUM(elders),0))
                ) as total 
    FROM members 
        LEFT JOIN company 
        ON company.companyId = members.companyId
    GROUP BY company.companyId

My tables

            company table
    +----------------------+
    | companyId |  Name    |
    +-----------+----------+
    |  1        | Company1 |
    |  2        | Company2 |
    +-----------+----------+

                       members table
    +--------------------------------------------+
    |  id  | companyId | gender | adult | elders |
    +------+-----------+--------+-------+--------+
    |   1  |     1     |  male  |  200  |   700  |                                      
    |   1  |     1     | female |  300  |    50  |                                      
    |   1  |     2     |  male  |  100  |   500  |                                      
    |   1  |     2     | female |  900  |   800  |                                      
    +------+-----------+--------+-------+--------+

                    expected Results table
    +-------------------------------------------------+
    | companyId |  Name    | adult | elders | Total   |
    +-----------+----------+-------+--------+---------+
    |  1        | Company1 | 500   |  750   |  1250   |
    |  1        | Company2 | 1000  |  1300  |  2300   |
    +-----------+----------+-------+--------+---------+

Thanks in advance

Upvotes: 0

Views: 645

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

A LEFT JOIN starting on members is not appropriate, unless members could lack a company. I'll just use a regular JOIN.

Then you want aggregation with arithmetic:

SELECT c.companyId, c.name,
       SUM(m.adults + m.elders) as total,
       SUM(m.adults) as adults, SUM(m.elders) as elders
FROM members m JOIN 
     company c
     ON c.companyId = m.companyId
GROUP BY c.companyId, c.name;

You would use a LEFT JOIN if one of the following were true and you wanted to avoid filtering rows out:

  • Some members do not have a valid companyId.
  • Some companies have no rows in members.

Upvotes: 1

Related Questions