CODEWITHSUNDEEP
mysql
groo
groo

Reputation: 13

mysql : display multiple results with heading

I'm a complete newb so forgive me.

I'm trying to get the results to display 2 or more different headings.

SELECT sum(fare) AS totalfare, count(*) AS fare10 
where fare>10
FROM tbl

I'm trying to get the WHERE statement apply to only count, not the sum, and have the result display as "totalfare" "fare10"

SELECT sum(fare) AS totalfare
FROM tbl
union
SELECT count(*) AS watever 
FROM tbl 
where fare > 10

I've tried this way, but the result grid would spit out the answers under 1 heading as totalfare. Is it possible to display it as totalfare | whatever?

Upvotes: 0

Views: 49

Answers (2)

Alexander R.
Alexander R.

Reputation: 1756

Finally you explained your question. You can do UNION only when you have tables (result sets) with the same fields. This is what you need. The above query selects directly from the derived table created by the two sub-queries.

SELECT 
    *
FROM
    (SELECT 
        SUM(fare) AS totalfare
    FROM
        tbl) a,
    (SELECT 
        COUNT(*) AS watever
    FROM
        tbl
    WHERE
        fare > 10) b

You will get results as one row

[ totalfare | watever ]
   number      number

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You want conditional aggregation:

SELECT sum(fare) AS totalfare,
       sum(case when fare > 10 then 1 else 0 end) as fare10
FROM tbl;

In MySQL you can also shorten this to:

SELECT sum(fare) AS totalfare,
       sum(fare > 10) as fare10
FROM tbl;

Upvotes: 0

Related Questions