how to get records without using group by condition in mysql

Question

0.00/5 (No votes)

See more:

i have the follwing table sructures

cust_inf(cust_id,cust_name,address,contactperson)

orders(order_no,cust_id,product_name,order_qty,order_amount,order_date)

invoices(inv_key,order_no,inv_date,cust_id,order_date,productname,inv_qty,inv_amount,Lr_no,
despatch_thr)

sample data is

cust_info
---------

<pre lang="SQL">
cust_id|cust_name|adress  |contactperson
---------------------------------------
1      | xxx     |  aaa   |vvv
2      | yyy     |  bbb   |nnn
---------------------------------------

orders
-------

VB

lang="SQL"></pre>
+----------+---------+---------------------------+-----------+--------------+-----------
| order_no | cust_id | product_name              | order_qty | order_amount |order_date
+----------+---------+---------------------------+-----------+--------------+-----------<pre
| o1       |       2 | Provel 200gms Powder      |        12 |          456 | 2013-11-06
| o1       |       2 | Calcy - D 10X2X10         |        15 |          810 | 2013-11-06
| o4       |       2 | Obact-200mg 10 X10 Tab    |         8 |          960 | 2013-11-06
| o4       |       2 | Obact-OZ 10 X10 Tab       |         7 |         1050 | 2013-11-06
| o4       |       2 | Omcid - D 10X15           |        12 |         1320 | 2013-11-06
| o2       |       1 | Pragmox -250mg 20 X10 Cap |        10 |         2330 | 2013-11-07
| o2       |       1 | Provel 200gms Powder      |        15 |          465 | 2013-11-07
| o2       |       1 | Obact-200mg 10 X10 Tab    |        12 |         1320 | 2013-11-07
| o2       |       1 | Obact-400mg 10 X10 Tab    |         8 |         1872 | 2013-11-07
| o2       |       1 | Obact-OZ 10 X10 Tab       |         7 |         1260 | 2013-11-08
| o2       |       1 | Omcid - D 10X15           |        12 |         1224 | 2013-11-07
| o3       |       3 | Provel 200gms Powder      |        88 |          777 | 2013-11-21
+----------+---------+---------------------------+-----------+--------------+-----------

invoices
--------

SQL

+-----------+----------+------------+---------+------------+
| inv_key   | order_no | inv_date   | cust_id | order_date |
+-----------+----------+------------+---------+------------+
| EDPL-1253 | o4       | 2013-11-07 |       2 | 2013-11-06 |
| EDPL-1253 | o4       | 2013-11-07 |       2 | 2013-11-06 |
| EDPL-1253 | o4       | 2013-11-07 |       2 | 2013-11-06 |
| EDPL-1253 | o1       | 2013-11-07 |       2 | 2013-11-06 |
| EDPL-1422 | o2       | 2013-11-08 |       1 | 2013-11-07 |
| EDPL-1422 | o2       | 2013-11-08 |       1 | 2013-11-07 |
| EDPL-1422 | o2       | 2013-11-08 |       1 | 2013-11-07 |
| EDPL-1422 | o2       | 2013-11-08 |       1 | 2013-11-07 |
| EDPL-1422 | o2       | 2013-11-08 |       1 | 2013-11-07 |
| EDPL-1422 | o2       | 2013-11-08 |       1 | 2013-11-07 |
| EDPL-1423 | o3       | 2013-11-21 |       3 | 2013-11-21 |
| EDPL-1253 | o1       | 2013-11-07 |       2 | 2013-11-06 |
| EDPL-1254 | o4       | 2013-11-19 |       2 | 2013-11-06 |
+-----------+----------+------------+---------+------------+


+---------------------------+---------+------------+
| productname               | inv_qty | inv_amount |
+---------------------------+---------+------------+
| Omcid - D 10X15           |       5 |       1000 |
| Obact-OZ 10 X10 Tab       |       7 |        900 |
| Obact-200mg 10 X10 Tab    |       8 |        720 |
| Calcy - D 10X2X10         |      15 |        540 |
| Pragmox -250mg 20 X10 Cap |       8 |       1864 |
| Provel 200gms Powder      |      12 |        372 |
| Obact-200mg 10 X10 Tab    |      12 |       1320 |
| Obact-400mg 10 X10 Tab    |       7 |       1638 |
| Obact-OZ 10 X10 Tab       |       7 |       1260 |
| Omcid - D 10X15           |      12 |       1224 |
| Provel 200gms Powder      |      88 |        777 |
| Provel 200gms Powder      |      12 |        540 |
| Omcid - D 10X15           |       7 |        320 |
+---------------------------+---------+------------+

my requirement is get the all cust related order details and invoice details like
my query is

SQL

select cust_name,address,contactperson,phone,
group_concat(orders.order_no order by orders.order_no asc) as order_no,
group_concat(product_name) As or_product,
group_concat(order_qty) As order_qty ,
group_concat(if(inv_qty is null,0,inv_qty)) as inv_qty,
group_concat(orders.order_date) As order_date from cust_info 
join orders on cust_info.cust_id=orders.cust_id 
left outer join invoices on cust_info.cust_id=invoices.cust_id 
and orders.product_name=invoices.productname and orders.order_date=invoices.order_date 
group by cust_name,orders.order_no;

result is

(cust_name, address, contactperson, phone, order_no, or_product, order_qty, inv_qty, order_date) VALUES
(' Srinnivas Biotech', '45-120/94, Manju shanker Aparments,Bblock', 'Mr. Shiva Kumar', '9883883883', 'o3', 'Provel 200gms Powder', '88', '88', '2013-11-21'),
('M/s Sri baba Enterprices', '#11-13-23,Sammetavari street,Near ramanaiah cool drink shop,Vijayawada-520001', 'Mr.Prasad', '9346560014', 'o2,o2,o2,o2,o2,o2', 'Provel 200gms Powder,Pragmox -250mg 20 X10 Cap,Omcid - D 10X15,Obact-OZ 10 X10 Tab,Obact-400mg 10 X10 Tab,Obact-200mg 10 X10 Tab', '15,10,12,7,8,12', '12,8,12,0,7,12', '2013-11-07,2013-11-07,2013-11-07,2013-11-08,2013-11-07,2013-11-07'),
('Madhav pharma', 'H No:18-19--99 kaimabad,Warangal-506002', 'Mr.Sammaiah', '9390115133', 'o1,o1', 'Calcy - D 10X2X10,Provel 200gms Powder', '15,12', '15,12', '2013-11-06,2013-11-06'),
('Madhav pharma', 'H No:18-19--99 kaimabad,Warangal-506002', 'Mr.Sammaiah', '9390115133', 'o4,o4,o4,o4', 'Obact-200mg 10 X10 Tab,Omcid - D 10X15,Omcid - D 10X15,Obact-OZ 10 X10 Tab', '8,12,12,7', '8,7,5,7', '2013-11-06,2013-11-06,2013-11-06,2013-11-06')

in or_product field contains same product name, if product name name is same then add their inv_qty inside the group concat(add inv_qty then group concat) but it is not working for me. i used the following query;

SQL

select cust_name,address,contactperson,phone,
 group_concat(orders.order_no order by orders.order_no asc) as order_no,
 group_concat(product_name) As or_product,
 group_concat(order_qty) As order_qty ,
 group_concat(case when count(invoices.productname)>1 then sum(ifnull(inv_qty,0) else inv_qty end) as inv_qty,
 group_concat(orders.order_date) As order_date from cust_info
 join orders on cust_info.cust_id=orders.cust_id
 left outer join invoices on cust_info.cust_id=invoices.cust_id
 and orders.product_name=invoices.productname and orders.order_date=invoices.order_date
 group by cust_name,orders.order_no

without using group by invoices.productname i need records. is there anyway to get the correct records please help me.

Thanks in advance

Posted 17-Nov-13 20:32pm

User-10031173

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-11-19T11:51:00

Solution 1

If you use aggregate functions[^], it's not possible to use it without GROUP BY statement!
You can try to achieve the same using loops[^], but i would not recommend you to use it for several reasons. One of them is a time of execution.

Posted 19-Nov-13 11:51am

Maciej Los