Dynamic Field in Select Query

[jeeva]

hi friends,

 

i have a table like

 

---------------------------------

id | Name   | Amount | Date 

---------------------------------

1  | pro1    | 500      | 2008-2-15

2  | pro2    | 800      | 2008-2-16

3  | pro3    | 500      | 2008-2-15

 

 

Now i want  report like

------------------------------------------

2008-2-15                |     2008-2-16

------------------------------------------

1000 [sum(Amount)]   |     800 [sum(Amount)]

 

 

i want get the date as a field

can get it like this?

 

 

[priti]

try,

SELECT sum( amount ) , date

FROM test

GROUP BY (

`date`

)

 

 

[jeeva]

many thanks priti,

it gives the result  like

------------------------------------------

sum                         |     Date

------------------------------------------

1000                        |     15-2-2008

800                          |     16-2-2008

 

 

Now, can i get the date as field like

 

------------------------------------------

2008-2-15                |     2008-2-16

------------------------------------------

1000                        |     800

 

 

 

is it possible?

 

 

 

[fenway]

Turning column values into column names is quite difficult to do "a priori".

[priti]

many thanks priti,

it gives the result  like

------------------------------------------

sum                         |     Date

------------------------------------------

1000                        |     15-2-2008

800                          |     16-2-2008

 

 

Now, can i get the date as field like

 

------------------------------------------

2008-2-15                |     2008-2-16

------------------------------------------

1000                        |     800

 

 

 

is it possible?

 

 

 

 

With mysql query i am not aware but you can get this in PHP reusltset and can easily handle the the way you want to show your results.

[aschk]

Ok that example set you have provided is fine, and possibly do-able with a pivot table (not examined yet so not sure), HOWEVER what happens when you have a record for EVERY day of the month. Are you expecting 31 columns each with the date in it? What about when you get one for every day of the year? 365 columns in your result set?

[aschk]

I've done you a pivot table version of this, but it occured to me you DON'T need the information in a pivot table.

What priti provided is in a better format than what you need it in.

 

Included is the pivot table that will give you "something" along the lines of what you are looking for, however it divides up by the name of the product. So it'll give you the summation for each "name" on the particular date.

SELECT name
      ,SUM(amount*(1-abs(sign(DATEDIFF(`date`,'2008-02-15'))))) as '2008-02-15'
      ,SUM(amount*(1-abs(sign(DATEDIFF(`date`,'2008-02-16'))))) as '2008-02-16'
FROM <table name here>
GROUP BY name;

[aschk]

If you use the ROLLUP option available with aggregate queries you can get a total result at the bottom. Personally i think this is a good report:

 

SELECT name
      ,SUM(amount*(1-abs(sign(DATEDIFF(`date`,'2008-02-15'))))) as '2008-02-15'
      ,SUM(amount*(1-abs(sign(DATEDIFF(`date`,'2008-02-16'))))) as '2008-02-16'
FROM date_amount_pivot
GROUP BY 1 WITH ROLLUP;

 

Also you can "trick" mysql into giving you a result you want, try this:

SELECT x.`2008-02-15`, x.`2008-02-16` FROM
(SELECT IFNULL(name,"Total")as name
      ,SUM(amount*(1-abs(sign(DATEDIFF(`date`,'2008-02-15'))))) as '2008-02-15'
      ,SUM(amount*(1-abs(sign(DATEDIFF(`date`,'2008-02-16'))))) as '2008-02-16'
FROM date_amount_pivot
GROUP BY name WITH ROLLUP) x
WHERE x.name = 'Total';

 

IMPORTANT NOTICE: I do NOT condone the usage of the above. I think it's "hacky", but feel free to use at your own discretion.