CODEWITHSUNDEEP
sql-serversql-server-2008group-by
user3127438
user3127438

Reputation: 49

sql server get several group by results from one query result

Is it possible to to retrieve several group by results from one query?

Currently I've a freetext book-title search system which returns the top X rows:

First it queries the book-titles

  SELECT TOP 16 grouped_location, grouped_author, book_title 
  FROM books 
  WHERE book_title like '%foo%'

then it queries the location group 

  SELECT grouped_location, COUNT(*)
  FROM books 
  WHERE book_title like '%foo%'
  GROUP BY  grouped_location

then it queries the author group: ....

Is it possible to retrieve this information with one search? I have no problem by sending multiple command to the SQL server, but the goal is that the SQL server only performs one search and not using up all resources by searching three times.

Please keep in mind that a client-side solution, by returning all records to the client and calculate the grouped results, is not an option. It requires to only return the TOP X records due to performance reasons.

Upvotes: 0

Views: 93

Answers (2)

ARA
ARA

Reputation: 1316

This query will give you row détails, with count by grouped_location for each row. Change the ORDER BY to meet your requirements

select top 16 grouped_location, grouped_author, book_title, 
    count(*) over (partition by grouped_location) as [count]
FROM books 
WHERE book_title like '%foo%'
-- order by grouped_author or some other column
order by [count] desc

Upvotes: 1

M.Ali
M.Ali

Reputation: 69504

To see information only grouped by grouped_location, you could so something like this.. 

  SELECT grouped_location , COUNT(*) Totals
  FROM
  (
  SELECT TOP 16 grouped_location, grouped_author, book_title 
  FROM books 
  WHERE book_title like '%foo%'
  ORDER BY Some_Column
  ) Q
  GROUP BY grouped_location

To see information grouped by All the columns, you could so something like this..

  SELECT grouped_location, grouped_author, book_title, COUNT(*) Totals
  FROM
  (
  SELECT TOP 16 grouped_location, grouped_author, book_title 
  FROM books 
  WHERE book_title like '%foo%'
  ORDER BY Some_Column
  ) Q
  GROUP BY grouped_location, grouped_author, book_title

Upvotes: 0

Related Questions