sql query problem

Question

what is wrong with this query? each one of them works separately but they're not working after i write the select *

select * from 
(SELECT     COUNT(issuer_id) AS INSU_cnt, min_desc AS INSURANCE
FROM         issuer INNER JOIN code ON economy_sect = cod_id
WHERE     (min_desc = 't')
GROUP BY min_desc)
UNION 
(SELECT     COUNT(issuer_id) AS Bank_cnt,min_desc AS BANKS
FROM         issuer INNER JOIN code ON economy_sect = cod_id
WHERE     (min_desc = 'b')
GROUP BY min_desc
)

edit: I guess the problem is that I have all the data I want in one table (with a query) and I want to select 5 rows from that table... I have this table:

Name        Count
Bank        20
Insurance  10
Econ        10

and I want the select statement to be something like this: select bank_count, insurance_count, econ_count and I thought a union might solve my problem, but it doesn't make much sense...

Answer 1

Instead of a UNION, use a IN clause, like this:

SELECT COUNT(issuer_id) AS Count, min_desc
FROM issuer INNER JOIN code ON economy_sect = cod_id
WHERE min_desc IN('t', 'b')
GROUP BY min_desc

With the UNION approach you're only going to get the column named from the first query anyway, so since that's already gone you might as well do it in a single query :)

---

For your updated question: this varies based on which database you're using, but something like a CASE statement would work:

SELECT (CASE min_desc WHEN 't' THEN 'Insurance'
                      WHEN 'b' THEN 'Bank'
                      WHEN 'e' THEN 'Econ'
                      ELSE 'Other'
        End) as Name, COUNT(issuer_id) AS Count
FROM issuer INNER JOIN code ON economy_sect = cod_id
WHERE min_desc IN('t', 'b')
GROUP BY min_desc

Or if you had a description table you could join to it gets cleaner/more maintainable...there are several ways to approach this problem, I'm not sure what your options or platform are.

Answer 2

I think the specific problem with the query that you posted is that you need parentheses:

SELECT * FROM (
    (
        SELECT    COUNT(issuer_id) AS INSU_cnt, min_desc AS INSURANCE
        FROM      issuer INNER JOIN code ON economy_sect = cod_id
        WHERE     min_desc = 't'
        GROUP BY  min_desc
    )
    UNION 
    (
        SELECT    COUNT(issuer_id) AS Bank_cnt, min_desc AS BANKS
        FROM      issuer INNER JOIN code ON economy_sect = cod_id
        WHERE     min_desc = 'b'
        GROUP BY  min_desc
    )
) T1

However this query is not actually what you want to do. What you actually want to do is this:

SELECT
(
    SELECT COUNT(issuer_id)
    FROM issuer INNER JOIN code ON economy_sect = cod_id
    WHERE min_desc = 't'
) AS ins_cnt,
    SELECT COUNT(issuer_id)
    FROM issuer INNER JOIN code ON economy_sect = cod_id
    WHERE min_desc = 'b'
) AS bank_cnt

Answer 3

With UNION, you're appending two result sets into a single table. Most SQL implementations will expect the columns from each of the sets being UNIONed together to have the same names. In your case, change the names of INSU_cnt and BANK_cnt to something generic (perhaps 'cnt') and INSURANCE and BANKS to something generic too.

Alternatively, there's UNION CORRESPONDING, which handles different column names by creating separate columns for them, but I don't think that's what you're after.

If this doesn't help, perhaps post the error you're getting from your log.

Answer 4

This is what it should be

select count(issuer_id) as cnt, min_desc
from issue inner join code on economy_sect = cod_id
where min_desc in ('t', 'b')
group by min_desc

Answer 5

Thank you so much everyone, Mark's solution has worked the best for me. I want to vote his answer as my solution but I can't figure out how :$ I'm new to this site...