aggregate-functions

Is it possible to perform a bitwise group function?

I have a field in a table which contains bitwise flags. Let's say for the sake of example there are three flags: 4 => read, 2 => write, 1 => execute and the table looks like this*: user_id | file | permissions -----------+--------+--------------- 1 | a.txt | 6 ( <-- 6 = 4 + 2 = read + write) 1 | b.txt | 4 ...

"Indexing" (aka maintaining a table of) aggregate data in SQL Server 2005

I have a table which maintains performance data of a system, each record is a call made to some important method and consists of the method name, its duration and a token - each request to the system is given a unique token and so all of the records with the same token are the same request, e.g: CallName Duration Token -----------...

TSQL Group By including all columns

I have a TSQL Query that does something like this: SELECT SUM(s.Amount) as TotalSales, p.ProductName FROM SALES s INNER JOIN Product p ON s.ProductID = p.ID GROUP BY p.ProductName The resulting output is TotalSales Product ----------------------- 123.45 Apples 234.56 Oranges 345.67 Grapes What I would like to do is...

How to Calculate Median?

I need to calculate a median on a set of data, so I created a temp table and have tried to follow some articles online with zero success, here is what I am working with: CREATE TABLE #QuizTemp (QuizProfileID INT,Cnt INT,TotalScore INT) INSERT INTO #QuizTemp SELECT QuizAnswers.QuizProfileID, COUNT(QuizAnswers.QuizProfileID) AS Cnt, SUM(...

Multiple SUM using LINQ

I have a loop like the following, can I do the same using multiple SUM? foreach (var detail in ArticleLedgerEntries.Where(pd => pd.LedgerEntryType == LedgerEntryTypeTypes.Unload && pd.InventoryType == InventoryTypes.Finished)) { weight += detail.GrossWeight; lengt...

TSQL: Cannot perform an aggregate function AVG on COUNT(*) to find busiest hours of day

Consider a SQL Server table that holds log data. The important parts are: CREATE TABLE [dbo].[CustomerLog]( [ID] [int] IDENTITY(1,1) NOT NULL, [CustID] [int] NOT NULL, [VisitDate] [datetime] NOT NULL, CONSTRAINT [PK_CustomerLog] PRIMARY KEY CLUSTERED ([ID] ASC)) ON [PRIMARY] The query here is around finding the distributi...

Aggregate functions - getting columns not included in the GROUP BY clause

I have a table which has three fields (item_id, source, and price). (item_id, source, and price) together make up the primary key. item_id source price ------- ------- ----- 1 paris 13 1 london 12 1 newyork 15 2 paris 22 2 london 15 2 newyork...

Using Aggregate Functions Inside a Trigger In MySQL

I have a 'People' table with several attributes including 'age'. Each time I insert a new tuple into this table, I would like to find out the average age of all the people listed in the table. If the average is above 50, I want to modify the age in the tuple being inserted. I'm using a 'BEFORE INSERT' trigger for this. Here is the test c...

MYSQL fetch 10 posts, each w/ vote count, sorted by vote count, limited by where clause on posts

I want to fetch a set of Posts w/ vote count listed, sorted by vote count (e.g.) Post 1 - Post Body blah blah - Votes: 500 Post 2 - Post Body blah blah - Votes: 400 Post 3 - Post Body blah blah - Votes: 300 Post 4 - Post Body blah blah - Votes: 200 I have 2 tables: Posts - columns - id, body, is_hidden Votes - columns - id, post_i...

Help on "GROUP BY clause" error

Hi Guys.. Wish anybody can help me with this error "Column 'Sales.No_' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."... I spent few days to understand this error but failed :( My Query:- SELECT SH.[No_], SH.[Sell-to Customer No_], SH.[Sell-to Contact No_], SH.[Sell-to...

Aggregrate all a single column in a grouped resultset in one query: is it possible?

At the moment I have the following MySQL query: SELECT COUNT(*) AS `count`, `v`.`value` FROM `client_entity_int` AS `v` INNER JOIN `client_entity` AS `e` ON e.id = v.entity_id WHERE (v.attribute_id = '1') AND (e.deleted = 0) GROUP BY `v`.`value` Which returns the following: +-------+-------+ | count | value | +-------+...

Oracle Function: Replicate wm_concat

I currently am working on a project within Crystal Reports that refuses to use the undocumented function WM_CONCAT, which is allowable within Oracle 10g. Here is the WM_CONCAT header information WM_CONCAT(p1 IN VARCHAR2) RETURN VARCHAR2 To use WM_CONCAT I pass it the following: WM_CONCAT(column1); This function seems to accept a co...

MySQL problem: Merging multiple results/rows caused by n-m

Hi everybody, i tried now nearly everything i could to solve he following problem. So far with no success. But there must be a solution cause i dont think the case is s.th too special. I think i am just a bloody beginner ;-) I need to join union merge or whatever ;-) in MySQL to solve the following problem... CASE: Three tables "posts"...

SQL - ID is lost after performing MIN then GROUP BY

To simplify, if I had a table called 'employee': id INT NOT NULL PRIMARY KEY, salary FLOAT, department VARCHAR(255) I want to perform and query where I retrieve the minimum salary in each department. So my query is something like this: SELECT employee.ID, MIN(employee.salary), employee.department FROM employee GROUP BY employee.depar...

SQLite3 Doesn't Produce an Aggregation Error on Malformed/Indeterminate Queries?

SQLite behaves differently when dealing with aggregation than many other RDBMS's. Consider the following table and values: create table foo (a int, b int); insert into foo (a, b) values (1, 10); insert into foo (a, b) values (2, 11); insert into foo (a, b) values (3, 12); If I query it thus: select a, group_concat(b) from foo; Norm...

Rename data, then delete duplicates

I've got a large dataset, some of which is is duplicate records, which are identifiable by dupes in two fields. To find these records, the following query works: SELECT * FROM supplierstuffs GROUP BY "Supplier Code", "Cost ex Tax" HAVING count("Description") > 1 Basically what I want to do is cat together all the values of "Descripti...

SQL Server Group By which counts occurrences of a score

Hi, This might be a bit difficult to explain but I have two columns in my SQL server database which I have simplified... Items ID itemName voteCount score Votes itemID score So, basically I am storing every vote that is placed in one table, but also a count of the number of votes for each item in the item table along with it's averag...

SQL Server-- join tables and SUM columns for each distinct row

Hi all, Could someone double check my SQL statement for proper operation and general commonsense approach? Here's what's going on: I have a parent and child table with a one-to-many relationship, joined on a column named AccountNumberKey. The child table has numeric columns in it I need to sum up. The data is such that all child r...

Trying to design a column that should sum values from another table

Hi everyone, sorry if the title is not very clear. I will try to explain now: I have two tables: table A and table B. The relation between them is one (for A table) to many (for B table). So, it's something like master-detail situation. I have a column 'Amount' in table B that is obviously decimal and a column 'TotalAmount' in table A....

Is this LINQ query with averaging and grouping by hour written efficiently?

This is my first real-world LINQ-to-SQL query. I was wondering if I am making any large, obvious mistakes. I have a medium-large sized (2M+ records, adding 13k a day) table with data, dataTypeID, machineID, and dateStamp. I'd like to get the average, min, and max of data from all machines and of a specific dataType within a 4 hour peri...