CODEWITHSUNDEEP
sqlnetezza
Will
Will

Reputation: 8641

Creating Custom Tables from DB results

I need to create a way to display my results from an SQL query in multiple columns. The sql is pretty basic it's a count of each entry of a certain type.

so the SQL is something like

Select count(distinct(id)) from Table where id_type = a

Select count(distinct(id)) from Table where id_type = b

Select count(distinct(id)) from Table where id_type = c

etc

I want these displayed in a table with one row which will give the count of each type under a column with a custom name.

My SQL is rather sparse, so additional outside info always welcome.

Upvotes: 0

Views: 112

Answers (1)

Taryn
Taryn

Reputation: 247860

It sounds like you want the pivot the data from the rows into columns. If that is the case in MySQL you will need to use an aggregate function with a CASE expression to perform this data transformation:

Select 
  count(distinct case when id_type = 'a' then id end) TotalA,
  count(distinct case when id_type = 'b' then id end) TotalB,
  count(distinct case when id_type = 'c' then id end) TotalC
from Table

Or if you still want to use the separate queries for some reason, then you could use a UNION ALL and then rotate the data into columns:

select 
  max(case when col = 'A' then TotalCount end) TotalA,
  max(case when col = 'B' then TotalCount end) TotalB,
  max(case when col = 'C' then TotalCount end) TotalC
from
(
  Select count(distinct(id)) TotalCount, 'A' Col
  from Table 
  where id_type = 'a'
  union all
  Select count(distinct(id)) TotalCount, 'B' Col
  from Table 
  where id_type = 'b'
  union all
  Select count(distinct(id)) TotalCount, 'C' Col
  from Table 
  where id_type = 'c'
) src

Upvotes: 2

Related Questions