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...
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...
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 ...
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?
...
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.
...
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...
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 ...
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
...
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...
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...
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...
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?
...
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...
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...
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.
...
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
...
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...
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...
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_...
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...