Home » Open Source » MySQL » New REPORT
New REPORT [message #646075] Fri, 18 December 2015 23:53 Go to next message
Danny Freeman
Messages: 21
Registered: December 2015
Location: UK
Junior Member
Hello all,
I am new in this database world and my question is that I want to generate one ERP report for client in this format :
Name Email Subtotal Shipping Total Created at
#71410 103034006@qq.com 162.22 42 204.22 2015-12-11 06:04:21 +0100
#71410 103034006@qq.com 2015-12-11 06:04:21 +0100
#71410 103034006@qq.com 2015-12-11 06:04:21 +0100


There are six columns in which I have to produce my output. I want to show the subtotal which is sum of three products,total which is sum of subtotal and shipping in one single row. I don't want to show subtotal,shipping and total in all the three rows. Pleae help me. Before this ERP report client had asked me to show the output in single row. So by using group concat i was able to show the correct output. But now I have to show report in this format. Please help me in this. I have attached the file. In this format client wants the report. Please help me
  • Attachment: 71459 (1).csv
    (Size: 101.93KB, Downloaded 3012 times)
Re: New REPORT [message #646076 is a reply to message #646075] Sat, 19 December 2015 00:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: New REPORT [message #646123 is a reply to message #646076] Sun, 20 December 2015 05:12 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What tool do you use (when creating reports)? Is it SQL*Plus? If not, what is it?
Re: New REPORT [message #646224 is a reply to message #646123] Tue, 22 December 2015 07:55 Go to previous messageGo to next message
Danny Freeman
Messages: 21
Registered: December 2015
Location: UK
Junior Member
Sorry for my late reply. Actually, I am using PHPMyadmin. My tables are all MYSQL tables.I did ask this question on various MYSQL forums but no one replied. I have not received any reply from any MYSQL forums. Please help me in this.
Re: New REPORT [message #646225 is a reply to message #646224] Tue, 22 December 2015 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This does not change the fact you have to post a test case.
So please help us to help you if you want to be helped.

Re: New REPORT [message #646229 is a reply to message #646225] Tue, 22 December 2015 09:15 Go to previous messageGo to next message
Danny Freeman
Messages: 21
Registered: December 2015
Location: UK
Junior Member
OK very sorry.
I had written following query to produce the previous report which was client asking.In which I had to show the details in one single line. I mean subtotal which is sum of all the products. And Total which is sum of shipping plus subtotal. Now, client asking me to produce the output as shown in the attached csv file in which he still wants subtotal and total in single line but he doesn't want the grouping of names,email. I don't know how to write that code.
SELECT s_order_details.ordernumber, 
       Group_concat(s_order_details.name SEPARATOR '/'), 
       Group_concat(s_order_details.price SEPARATOR '/'), 
       s_order.currency, 
       Sum(s_order_details.quantity), 
       Sum(s_order_details.price * s_order_details.quantity), 
       Group_concat(s_order_details.articleordernumber SEPARATOR '/'), 
       s_order.invoice_shipping, 
       s_order.invoice_shipping 
       + Sum(s_order_details.price), 
       Concat_ws(' ', s_order_shippingaddress.firstname, NULL, 
       s_order_shippingaddress.lastname), 
       s_order_shippingaddress.street, 
       s_order_shippingaddress.zipcode, 
       s_order_shippingaddress.city, 
       s_order_shippingaddress.company, 
       Concat_ws(' ', s_order_billingaddress.firstname, NULL, 
       s_order_billingaddress.lastname), 
       s_order_billingaddress.street, 
       s_order_billingaddress.zipcode, 
       s_order_billingaddress.city, 
       s_order_billingaddress.company, 
       s_order_billingaddress.phone 
FROM   s_order_details 
       INNER JOIN s_order_shippingaddress 
               ON s_order_details.orderid = s_order_shippingaddress.orderid 
       INNER JOIN s_order_billingaddress 
               ON s_order_details.orderid = s_order_billingaddress.orderid 
       INNER JOIN s_order 
               ON s_order.ordernumber = s_order_details.ordernumber 
WHERE  s_order_details.ordernumber = 20005 
GROUP  BY s_order_details.ordernumber 
Re: New REPORT [message #646230 is a reply to message #646229] Tue, 22 December 2015 09:24 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How can we do anything without any table and data?

Previous Topic: How to backup Mysql DB
Next Topic: Names along with sum(sal)
Goto Forum:
  


Current Time: Thu Mar 28 16:28:36 CDT 2024