group-by

How can I sort data before grouping in MYSQL

I have a big mysql query which needs to trawl through 4 tables to get all the 'items' for my application. Each item can have many categories and each user can have up to one of each item. The items and categories are easy: SELECT Items.itemId, Items.name, Items.type, Categories.name AS category FROM Items LEFT JOIN ItemCategories ON I...

My LINQ is really inefficent

I'm using LINQPad to learn LINQ and I've run into a stumbling block. The goal is to get a list of Network Ids, Network Names and how many Stations each has. Here is my original SQL: SELECT n.iStationId AS NetworkID, n.sPrettyName AS NetworkName, COUNT(s.iStationID) AS StationCount FROM T_StationInfo AS s, T_StationInfo as n WHERE s.iN...

SQL Question ... should I use a Group By???

I have a situation where I need to group data by a portfolio name, then sort through the data and display check boxes. Here's the table structure. Table A -------- portfolio_id portfolio_name Table B ------- file_id portfolio_id (Foreign Key fk_port_id references portfolio_id in table A) file_name Basically Table A links to table B ...

MYSQL COUNT GROUP BY question

I'd like to only select the rows where the count is greater than 1 (in other words the duplicates) right now from a few thousand records i am mostly seeing ones with a few 2s and 3s here and there SELECT count( * ) AS `Number` , GI . * FROM `GeneralInformation` AS GI GROUP BY `FirstName` , `Surname` how can I do this? ...

group by price range

Say I have a table of real estate properties: A 15,000 B 50,000 C 100,000 D 25,000 I'd like to group them by 0 - 49,999, 50,000 - 99,999 and 100,000 - 200,000 So the result should be: 0 - 49k (2) 50k - 99k (1) 100k - 200k (1) Is there a way to do that in one SQL statement? I'm using Postgres by the way. ...

SQL Server: Group results on time interval

Hey guys, I've got an MS SQL Server table that records our plant's alarm events with a row for each alarm and a datetime column to capture when the alarm happened. We run our plant in 12 hour shifts (6 am to 6pm, 6pm to 6am) and I need to figure out how many alarms we're getting each shift. How do I group my results to get that? The...

Algorithm in the tag clouds (Calculating)

I have a tag list which contains a list of duplicated tags, such as "tag1", "tag1", "tag2", "tag3", "tag3" and "tag2". I want to calculate and how many tag1, tag2 and tag3 are in the list, and then add them into a new tag list such as var tagList = new List<Tag>(), it will have thousands of the tags in the list. Can anyone one help me ...

Linq problem with group by clause

Hi all, I have some problems in executing a Linq query with the group by clause in a C# project. Here following the query: var items = ( from controlForm in dataContext.ControlForms join controlFormStatus in dataContext.ControlFormStatus on controlForm.FK_ControlFormStatus equals controlFormStatus.Id ...

Linq query with group by retrieving percentage

Hi all! I have to calculate the percentage in the group by clause. More in depth I need to calculate the percentage of ControlForms that have Status = 'False'. The Status is in the table checkResult. var items = (from controlForm in dataContext.ControlForms join controlFormStatus in dataContext.ControlFormStatus on controlF...

ordered SQL Select columnwise distinct but return of all columns

I have a little SQL Distinct puzzle that i cannot solve (or at least not in an very elegant way). I have two tables (try to ignore the simplicity of the example). I'm using MSSQL 2008 if that makes much of a difference. Table: Category | categoryId (uniqueidentifier) PK | | Name varchar(50) | Table: Download | do...

Why doesn't Postgres Group By NULL select counts?

I am trying to get a count of each value in a table using the following SQL: SELECT col, COUNT(col) FROM table GROUP BY col (There's a WHERE clause in the real code, but it has no impact). When I run this I get results like so: a - 5 b - 4 <null> - 0 It doesn't matter how many null entries I have, it always shows a coun...

Getting the latest entries for grouped by the foreign key in MySQL

Hi! I got the table with the fields: id, foreign_key, created, modified It's a table that logs the changes of a part of my program. What I want is to retrieve the latest logs grouped by the foreign key. How do I do this? EDIT: to summarize, how do I order first before I group? ...

Strange MySQL GROUP BY/ORDER BY behaviour

SELECT p.price, h.name AS hotel_name FROM prices p LEFT JOIN hotels h ON p.hotel_id = h.id WHERE p.city = 'boedapest' AND p.hotel_id IS NOT NULL GROUP BY p.name ORDER BY p.price ASC RESULT: 26 Eben Hotel Budapest 27 Ve...

In C# problem grouping by value and then summing

I am trying to sum the cost for a series of items grouped by a person's organization. I believe the summation is working correctly but I am not seeing my grouping. The objects stored in rollup just contain the value of the summation. I'd expect them to have the organization in addition to the sum of the cost for that organization. What h...

MySql Group function error

select nume,model,sum(km_sosire-km_plecare) as 'km_parcursi' from masina m inner join (foaie_parcurs f inner join angajat a using(id_angajat)) using(id_masina) where sum(km_sosire-km_plecare)>100 group by a.nume,m.model order by sum(km_sosire-km_plecare); Error: Invalid use of group function Why? Thanks. ...

Linq with group by having count

Hi, how do I write this query in linq (vb.net)? select B.Name from Company B group by B.Name having COUNT(1) > 1 ...

Mysql GROUP BY and unique values

I have a table in MySQL: SID DID MessageType DateOf Message 1 255 LOG 2010-01-17 15:02:05 Log msg 1 2 255 USER 2010-01-17 19:02:05 User msg 1 3 211 LOG 2010-01-17 15:06:55 Log msg 2 4 211 ADMIN 2010-01-17 22:06:55 Admin msg 1 I need to get last (by DateOf) Message and MessageType, groupped by DID, ord...

Mysql scenario - Get all tasks even if there is no entry?

Hi Gurus, I have three tables Tasks with columns Taskid, Taskname TaskAllocations with columns Taskid, EmpNum TaskEntries with columns TaskId, EmpNum, WorkedDate, Hoursspent Now I want to get all the task entries along a particular week. Here my problem is even if there is no Taskentry for a particular task I should get atleast a r...

SQL HAVING SUM GROUP BY

Using SQL Server 2005. I am building an inventory/purchasing program and I’m at the point where I need the user to “check out” equipment. When he selects a product, I need to query which stock locations have the available Qty, and tell the user which location to walk to/ retrieve product. Here is a query for a particular [StockLocation_...

Group by and non distinct columns and data normalization

Hello Everybody!! I have a large table(60 columns, 1.5 million records) of denormalized data in MS SQL 2005 that was imported from an Access database. I've been tasked with normalizing and inserting this data into our data model. I would like to create a query that used a grouping of, for example "customer_number", and returned a re...