How to remove the Group By clause?

Question

How to remove the Group By clause?

chef423

SSCertifiable

Points: 5279
More actions
March 5, 2015 at 5:31 pm

#295835

SELECT DISTINCT
'Banquets - All Day' as revName,
SUM(t.c_items_total) AS Banquet_Total,
SUM(t.cover_count) as Total_Covers,
-- (t.c_items_total) / (t.cover_count) as AvgPer_Cover--
FROM dbo.Ticket AS t JOIN
dbo.PeriodDefinition AS pd ON CAST(t.dt_close_time AS time) BETWEEN CAST(pd.dt_start AS time) AND CAST(pd.dt_finish AS time) AND
pd.i_period_definition_id IN(1, 2, 3, 4) and t.i_revcenter_id = 7 and t.i_void_ticket_id IS NULL and t.b_closed = 1 and t.c_items_total > 0
--GROUP BY t.c_items_total, t.cover_count--
The output needs to be grouped by the t.c_items_total...I just need the avg per cover (person) / items_total.
Any help or advice is much appreciated.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Stuart Davies SSCoach Points: 19505 More actions · Answer 1

Basically you can't. As soon as you start to use sum, count, min, max etc (i.e. aggregate functions) you must have a group by

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Can you post sample data and exactly what you want as results?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

roger.price-1150775 Default port Points: 1458 More actions · Answer 3

why do you want to remove the GROUP BY ? Is it stopping you doing something else or not working ?

Its not effecient but you can try sub queries.

SELECT

emp.empid,

total_sales = (SELECT SUM(value) FROM sales WHERE sales.empid = emp.empid),

last_sale (SELECT MAX(date) FROM sales WHERE sales.empid = emp.empid)

FROM emp

chef423 SSCertifiable Points: 5279 More actions · Answer 4

Here is how I resolved this. Basic. Thanks for all the responses, I love this forum, always friendly folks willing to share knowledge.

SELECT

'Banquets - All Day' as revName,

SUM(t.c_items_total) AS Banquet_Total,

SUM(t.cover_count) as Total_Covers,

SUM(t.c_items_total) / SUM(t.cover_count) as AvgPer_Cover

FROM

dbo.Ticket AS t JOIN

dbo.PeriodDefinition AS pd ON CAST(t.dt_close_time AS time) BETWEEN CAST(pd.dt_start AS time) AND CAST(pd.dt_finish AS time) AND

pd.i_period_definition_id = 2 and t.i_revcenter_id = 7 and t.i_void_ticket_id IS NULL and t.b_closed = 1