pigmentarts
asked on
Without Group By Clause
Hi All,
I'm trying to write this query to group results by ev_id but can't seem to once I start selecting from the order table (TAmount). The query is as follows:
SELECT E.ev_id,E.ev_name, (SELECT count(*) as evOrdTot from tbl_book_orders where or_ev_id = E.ev_id) AS totOrd
,( SELECT SUM(oi_qty * oi_price) AS TotAmount from tbl_book_orderItems WHERE oi_or_id = BO.or_id) AS TAmount
FROM tbl_booking_events E
LEFT JOIN tbl_book_orders BO ON E.ev_id = BO.or_ev_id
LEFT JOIN tbl_book_orderItems OI ON BO.or_id = OI.oi_or_id
Thanks
I'm trying to write this query to group results by ev_id but can't seem to once I start selecting from the order table (TAmount). The query is as follows:
SELECT E.ev_id,E.ev_name, (SELECT count(*) as evOrdTot from tbl_book_orders where or_ev_id = E.ev_id) AS totOrd
,( SELECT SUM(oi_qty * oi_price) AS TotAmount from tbl_book_orderItems WHERE oi_or_id = BO.or_id) AS TAmount
FROM tbl_booking_events E
LEFT JOIN tbl_book_orders BO ON E.ev_id = BO.or_ev_id
LEFT JOIN tbl_book_orderItems OI ON BO.or_id = OI.oi_or_id
Thanks
Are you getting any error
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In my query,
COUNT(BO.order_id) AS evOrdTot ,
replace by
COUNT(*) AS evOrdTot ,
What is your issue ?
Raj
COUNT(BO.order_id) AS evOrdTot ,
replace by
COUNT(*) AS evOrdTot ,
SELECT E.ev_id ,
E.ev_name ,
COUNT(BO.*) AS evOrdTot ,
SUM(OI.oi_qty * OI.oi_price) AS TotAmount
FROM tbl_booking_events E
LEFT JOIN tbl_book_orders BO ON E.ev_id = BO.or_ev_id
LEFT JOIN tbl_book_orderItems OI ON BO.or_id = OI.oi_or_id
GROUP BY E.ev_id ,
E.ev_name
What is your issue ?
Raj
Hi,
I think the author asked to execute the query without GROUP BY
If not getting an error, are the results unexpected?
This might be due to LEFT JOIN. If there is no match, it returns NULL. NULL multiplies with any other value is also NULL.
I think the author asked to execute the query without GROUP BY
If not getting an error, are the results unexpected?
This might be due to LEFT JOIN. If there is no match, it returns NULL. NULL multiplies with any other value is also NULL.
Sorry. I forgot about question title.
Without GROUP BY same ev_id may shown repeatly in the result with the same count and sum
Try INNER JOIN
Without GROUP BY same ev_id may shown repeatly in the result with the same count and sum
Try INNER JOIN
SELECT E.ev_id ,
E.ev_name ,
( SELECT COUNT(*) AS evOrdTot
FROM tbl_book_orders
WHERE or_ev_id = E.ev_id
) AS totOrd ,
( SELECT SUM(oi_qty * oi_price) AS TotAmount
FROM tbl_book_orderItems
WHERE oi_or_id = BO.or_id
) AS TAmount
FROM tbl_booking_events E
INNER JOIN tbl_book_orders BO ON E.ev_id = BO.or_ev_id
INNER JOIN tbl_book_orderItems OI ON BO.or_id = OI.oi_or_id
I suggest GROUP BY
@pigmentarts, Please explain so that we can get you
Raj
@pigmentarts, Please explain so that we can get you
Raj
ASKER
Well I am not getting any errors the query runs fine just that I am getting duplicate results which I don't want. I am trying to give users a summary of items ordered for an event but only want to show the event and a total of all orders placed for that event. I'm sure there may be a better way of writing it.
this article should be read: https://www.experts-exchange.com/A_3203.html
ASKER
Currently the results look as such with the event 'Test2' being repeated when it should be grouped with the one below. I need the Query to be fast as there will be alot of data to get through.
Event ev_id
Test2 1 1250
Test2 1 NULL
Test 2 NULL
Dinner 3 NULL
Event ev_id
Test2 1 1250
Test2 1 NULL
Test 2 NULL
Dinner 3 NULL
>> ..I am getting duplicate results ..
That I mentioned already-
Without GROUP BY same ev_id may shown repeatly in the result with the same count and sum
Try this query - http:#34996698
Why you are trying to avoid GROUP BY ?
Raj
That I mentioned already-
Without GROUP BY same ev_id may shown repeatly in the result with the same count and sum
Try this query - http:#34996698
Why you are trying to avoid GROUP BY ?
Raj
Also try your query itself by replacing LEFT JOIN with INNER JOIN - http:#34996734
NULL is because of LEFT JOIN.
Better option is using GROUP BY
NULL is because of LEFT JOIN.
Better option is using GROUP BY
ASKER
The only reason I was trying to avoid the Group By Clause is because they can be in-efficient when running against a database that has thousands of results which this will have. If this is the best way then I'll go ahead and use the Group By clause, I just thought there may have been a better way for writing this script.
ASKER
The query you provided 'RajkumarGS' seems to do the trick