Select and Union result into Crosstab-type in one query 1

[JDRoss]

Maybe this is impossible?

I have four tables of identical structure but not related.

I am running a Select on four tables with three Union All. I would like the result from the Select and Unions (which work fine) to be routed into a Crosstab-type query to gvie the result from each table in columns. But here is the difficult bit, I would like to do this in one query.

As I am new to MySQL, I am confused by subqueries and temporary tables and how to reference them, though I realise the latter may be the way to go.

Anyway, this is the code and I would appreciate some help or advice.

SELECT "T1" AS Table1,Custid,yearid, SUM(amount) AS "Total"
FROM table1
WHERE  yearid>0  
GROUP by yearid

UNION ALL
SELECT "T2" AS Table2,Custid,yearid, SUM(amount) 
FROM table2
WHERE  yearid>0  
GROUP by yearid

UNION ALL
SELECT "T3" AS Table3,Custid,yearid, SUM(amount) 
FROM table3
WHERE  yearid>0  
GROUP by yearid

UNION ALL
SELECT "T4" AS Table4,Custid,yearid, SUM(amount) 
FROM table4
WHERE  yearid>0  
GROUP by yearid

The above code takes it this far:

T1| 1| 2003| 20.00
T2| 1| 2003| 30.00
T3| 1| 2003| 45.00
T4| 1| 2003| 55.00


I would like to know how the results from the unions can pivot on the customer and year as follows:


CustId| Yearid| T1| T2| T3| T4
1 | 2004 | 20| 30| 40| 50
2 | 2005 | 10| 25| 67| 32


Any ideas?


John

 

[hvass]

Basic principle of generating a Xtab

create temporary table t
select
   "T1" as source,Custid,yearid,amount
from table1;
insert into t
select
   "T2" as source,Custid,yearid,amount
from table2;
insert into t
   "T3" as source,Custid,yearid,amount
from table3;
insert into t
   "T4" as source,Custid,yearid,amount
from table4;

select
   Custid,yearid,
   sum(if(source="T1",amount,0)) as T1,
   sum(if(source="T2",amount,0)) as T2,
   sum(if(source="T3",amount,0)) as T3,
   sum(if(source="T4",amount,0)) as T4
from t
group by Custid,yearid;

 

[JDRoss]

Thanks hvass,

This is very helpful. I cut and pasted the solution you suggested but I am getting a MySQL error around the first semi-colon.

I notice there are semi-colons after each Select, or Insert Into, I am sure they are all necessary?

Regards

John

 

[hvass]

Semi colons are needed in the mysql client that mysql.com distributes to distinquish between sql statements, they are needed for most GUI's like mysqlfront they are not needed if you are using an API like C, PHP or Perl as you only execute one statement at a time.. it just depends

I wrote the code off the top of my head so no guarantees that it is 100% syntax error free

 

[JDRoss]

I was not using MySQLFront, I was using something similar, I imagine, it is DBManager from DBTools Software. But I have downloaded MySQLFront since your message

When I run the code in this, I get the error message. ERROR 1064 (You have an error in your SQL syntax.

I have tried it with and without semicolons.

Do I need to tell it to ignore duplicates? could this be the problem?

Thanks for you help

JDRoss

 

[r937]

temporary tables are not necessary with subqueries

here it is in one query --

select custid,yearid
     , sum(T1sum) as T1
     , sum(T2sum) as T2
     , sum(T3sum) as T3
     , sum(T4sum) as T4
  from (     
       select custid,yearid
            , sum(amount) as T1sum
            , 0           as T2sum
            , 0           as T3sum
            , 0           as T4sum
         from table1
        where yearid>0  
       group by custid,yearid
       union all
       select custid,yearid,0,sum(amount),0,0
         from table2
        where yearid>0  
       group by custid,yearid
       union all
       select custid,yearid,0,0,sum(amount),0
         from table3
        where yearid>0  
       group by custid,yearid
       union all
       select custid,yearid,0,0,0,sum(amount)
         from table4
        where yearid>0  
       group by custid,yearid
       ) as dt
group by custid,yearid

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[hvass]

ERROR 1064 (You have an error in your SQL syntax
should tell you what the error is - what does it say

 

[hvass]

ERROR 1064 (You have an error in your SQL syntax
should tell you what the error is - what does it say

 

[JDRoss]

thanks r937, I will try your subquery solution too.

In response to the error 1064 syntax question, I have traced the problem to one of the tables. Possibly some blank DataTimeStamp fields. Mysqlfront was able to show me were it was stopping.

I have reimported the data for this table, I will let you both know how I get on.

Regards and much thanks.


John

 

[JDRoss]

create temporary table t
select
   "T1" as source,Custid,yearid,amount
from table1;
insert into t
select
   "T2" as source,Custid,yearid,amount
from table2;
insert into t
   "T3" as source,Custid,yearid,amount
from table3;
insert into t
   "T4" as source,Custid,yearid,amount
from table4;

select
   Custid,yearid,
   sum(if(source="T1",amount,0)) as T1,
   sum(if(source="T2",amount,0)) as T2,
   sum(if(source="T3",amount,0)) as T3,
   sum(if(source="T4",amount,0)) as T4,
   SUM(amount) AS "Total"
from t
group by Custid,yearid;

Hvass, I got your code to work. Thanks. I added this line:
[red]SUM(amount) AS "Total"[/red], which gives a total for the row across.

However, if I use a temp table, mysql gives an error if it already exists. How can I deal with this in a real connect situation?

John

 

[JDRoss]

r937


I have tried your subquery but I am getting a synta error around the following:

 select custid,yearid
            , sum(amount) as T1sum
            , 0           as T2sum

I can't seem to trace the source of this error.

Any ideas


JDRoss

 

[r937]

you're probably on an older version of mysql, that's all

nothing to worry about



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[Bastien]

create temp table t if not exists...

Bastien

Cat, the other other white meat