Problem with group by and order by

Question

Problem with group by and order by

abhas

SSCarpal Tunnel

Points: 4681
More actions
July 7, 2014 at 1:07 am

#296280

Hi All,
Iam having below schema.
CREATE TABLE #Turnover (
location varchar(50),
Total int
)
insert into #Turnover (location,Total) values('A', 500)
insert into #Turnover (location,Total) values('AB', 200)
insert into #Turnover (location,Total) values('ABC', 100)
insert into #Turnover (location,Total) values('BA', 100)
insert into #Turnover (location,Total) values('BAC', 500)
insert into #Turnover (location,Total) values('BAM', 100)
now i want output order by total but same time i want to create two groups. i.e.
location starting with A and order by total and after locations starting with B and order by total.
Any Idea Please?
Thanks
Abhas.

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

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Can you give an example of the desired output?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Louis Hillebrand SSCarpal Tunnel Points: 4970 More actions · Answer 2

You can try with this code:

SELECT LEFT(location,1),

SUM(Total)

FROM#Turnover

GROUPBY ROLLUP (LEFT(location,1));

This will give a total per group and a grand total.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

DROP TABLE #Turnover

CREATE TABLE #Turnover

(location varchar(50),Total int)

INSERT INTO #Turnover (location,Total) VALUES

('A', 500),

('AB', 200),

('ABC', 100),

('BA', 100),

('BAC', 500),

('BAM', 100)

SELECT NewGroup = LEFT(location,1), location, Total

FROM #Turnover

ORDER BY LEFT(location,1), Total

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 4

Thanks you

Chris and Louis. Its working.... :-):-):-)

Thanks,

Abhas.

rahulrathore.1986 Valued Member Points: 70 More actions · Answer 5

select * from #Turnover

select LEFT(location,1),Sum(Total) from #Turnover group by LEFT(location,1)