Creating Custom Tables from DB results

Question

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.

Do you mean pivot table http://en.wikibooks.org/wiki/MySQL/Pivot_table ? — piotrekkr, Jan 14 '13 at 10:49

score 2 · Answer 1 · answered Jan 14 '13 at 10:50

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

Creating Custom Tables from DB results

1 Answers1