StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is in SQL Server...but easy to convert for a MySQL guy I would think!</p> <pre><code>select top 5 c.name as CategoryName, sum(v.value) as VoteSum from categories c inner join items i on c.category_id = i.category_id inner join votes v on i.item_id = v.item_id where created_at between dateadd(week,-1,getdate()) and getdate() group by c.name order by sum(v.value) desc </code></pre> <p>select top 5 <strong>--number of categories to show in calculation</strong></p> <p>c.name as CategoryName, sum(v.value) as VoteSum <strong>--get the categoryname and the sum of votes</strong></p> <p>from categories c inner join items i <strong>--join the heirarchy: categories to items</strong></p> <p>on c.category_id = i.category_id <strong>--on category_id</strong></p> <p>inner join votes v <strong>--items to votes</strong></p> <p>on i.item_id = v.item_id <strong>--on item_id</strong></p> <p>where created_at between dateadd(week,-1,getdate()) and getdate() <strong>--specify the date range to include</strong></p> <p>group by c.name <strong>--group the results by the category</strong></p> <p>order by sum(v.value) desc <strong>--order the results by the summed value</strong></p> <p>Here is some sql (from SQL Server) to get the tables up and running:</p> <pre><code>CREATE TABLE [categories]( [category_id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](50) NOT NULL ) CREATE TABLE [items]( [item_id] [int] IDENTITY(1,1) NOT NULL, [category_id] [int] NOT NULL, [name] [varchar](50) ) CREATE TABLE [dbo].[votes]( [vote_id] [int] IDENTITY(1,1) NOT NULL, [value] [int] NOT NULL, [item_id] [int] NOT NULL, [created_at] [datetime] NOT NULL ) insert into categories (name) values (' asp.net ') insert into categories (name) values (' c# ') insert into categories (name) values (' vb ') insert into categories (name) values (' sql ') insert into categories (name) values (' html ') insert into categories (name) values (' javascript ') insert into items (category_id, name) values ( 1 ,' session handling ') insert into items (category_id, name) values ( 1 ,' mvc vs mvp ') insert into items (category_id, name) values ( 1 ,' code behind or no code behind ') insert into items (category_id, name) values ( 2 ,' LINQ? ') insert into items (category_id, name) values ( 2 ,' lamdas ') insert into items (category_id, name) values ( 2 ,' multi-threaded code ') insert into items (category_id, name) values ( 2 ,' SOLID principles ') insert into items (category_id, name) values ( 3 ,' vb vs C# ') insert into items (category_id, name) values ( 3 ,' VB.NET over vb6 ') insert into items (category_id, name) values ( 4 ,' CLR procedures or stored procedures ') insert into items (category_id, name) values ( 4 ,' ORMs vs stored procedures and views ') insert into items (category_id, name) values ( 6 ,' jquery instead of standard DOM ') insert into votes (value, item_id, created_at) values ( -1 , 1 ,' 6/26/2009 18:59 ') insert into votes (value, item_id, created_at) values ( 1 , 1 ,' 6/26/2009 18:59 ') insert into votes (value, item_id, created_at) values ( 3 , 1 ,' 6/26/2009 18:59 ') insert into votes (value, item_id, created_at) values ( 3 , 1 ,' 6/26/2009 18:59 ') insert into votes (value, item_id, created_at) values ( 2 , 2 ,' 6/26/2009 18:59 ') insert into votes (value, item_id, created_at) values ( 2 , 2 ,' 6/26/2009 18:59 ') insert into votes (value, item_id, created_at) values ( 4 , 2 ,' 6/26/2009 18:59 ') insert into votes (value, item_id, created_at) values ( -3 , 2 ,' 6/26/2009 18:59 ') insert into votes (value, item_id, created_at) values ( 2 , 4 ,' 6/26/2009 19:00 ') insert into votes (value, item_id, created_at) values ( 6 , 4 ,' 6/26/2009 19:00 ') insert into votes (value, item_id, created_at) values ( 3 , 4 ,' 6/26/2009 19:00 ') insert into votes (value, item_id, created_at) values ( 5 , 4 ,' 6/26/2009 19:00 ') insert into votes (value, item_id, created_at) values ( 8 , 7 ,' 6/26/2009 19:00 ') insert into votes (value, item_id, created_at) values ( 3 , 6 ,' 6/26/2009 19:00 ') insert into votes (value, item_id, created_at) values ( 8 , 7 ,' 6/26/2009 19:01 ') insert into votes (value, item_id, created_at) values ( 2 , 5 ,' 6/26/2009 19:01 ') </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload