1

I want to generate reprt like below. Date should come as columns, and each date sales across salesman and the total should come in rows. how can i get it thorugh MYSQL. My Table is X which is having all these details.

                |  Date 1    |  Date 2      |     Total          |
-----------------------------------------------------------------
SalesmanName    |Sales | Qty | Sales | Qty  | TotSales | TotQty  |
------------------------------------------------------------------
Salesman 1      | 100  | 10  | 100   |  10  |   200    |  20     |
------------------------------------------------------------------
Salesman 2      | 50   | 5   | 200   | 20   |   250    |  25     |
------------------------------------------------------------------

Table X: 

    salesmanname, salesvalue, salesquantity, transdate 
    (abc,         100,        10,             2015-07-01) 
    (xyz,          50,         5,             2015-07-01) 
    (abc,          90,         9,             2015-07-01) 
    (abc,          80,         8,             2015-07-01) 
    (xyz,          60,         6,             2015-07-02) 
    (abc,          40,         4,             2015-07-03) 
    (xyz,         100,        10,             2015-07-03) 
    (abc,          90,         9,             2015-07-04) 
    (xyz,          70,         7,             2015-07-04) 
    (xyz,          50,         5,             2015-07-04)

SQLFiddle : Table X data

i tried with

select salesmanname, sum(sales), sum(qty) from x where trnsdate BETWEEN fromdate AND todate group by trnsdate,salesmanname

But i am not able to make the columns . How can i do the same. Please help me, i am new to MySql and queries.

Ryan Vincent
  • 4,483
  • 7
  • 22
  • 31
Veena Sujith
  • 175
  • 1
  • 5
  • 16
  • Can you share some sample data so that we can work over there? – Abhishek Ginani Jul 26 '15 at 06:03
  • Assuming that the table given represents your desired output can you give us a sample of your input table `X`? – Carsten Massmann Jul 26 '15 at 06:04
  • salesmanname, salesvalue, salesquantity, transdate are the columns in table x Data as follows (abc, 100,10,2015-07-01) (xyz, 50,5,2015-07-01) (abc, 90,9,2015-07-01) (abc, 80,8,2015-07-01) (xyz, 60,6,2015-07-02) (abc, 40,4,2015-07-03) (xyz, 100,10,2015-07-03) (abc, 90,9,2015-07-04) (xyz, 70,7,2015-07-04) (xyz, 50,5,2015-07-04) – Veena Sujith Jul 26 '15 at 06:11
  • is it not posible to get wat i want using a MySql Query ?????? – Veena Sujith Jul 27 '15 at 05:44
  • maybe useful? [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table). Also: [Useful resource of MySQL queries and techniques](http://www.artfulsoftware.com/infotree/queries.php). I would do the reporting outside of `MySQL` as it is easier. – Ryan Vincent Jul 27 '15 at 07:52

0 Answers0