Need Help With QUERY And GROUP BY

Jul 23, 2004

I have a query that looks like this:
----------------------------------------------------------------------------
SELECT TOP 8 TABLEDATA.VW_DATA.COMPLETIONDATE AS COMPLETIONDATE,
TABLEDATA.VW_DATA.TYPE,
TABLEDATA.VW_DATA.TEAM_ID,
AVG(TABLEDATA.VW_DATA.DURATION) AS AVGDURATION,
TABLEDATA.VW_DATA.SITE,
TABLEDATA.MODELS.MODEL, TABLEDATA.TEAMS.DURATION,
TABLEDATA.TEAM_TYPE.TYPE AS TEAMTYPE
FROM TABLEDATA.VW_DATA
INNER JOIN
TABLEDATA.MODELS ON
TABLEDATA.VW_DATA.MODEL_ID = TABLEDATA.MODELS.MODEL_ID
INNER JOIN
TABLEDATA.TEAMS ON TABLEDATA.MODELS.MODEL_ID = TABLEDATA.TEAMS.MODEL_ID AND
TABLEDATA.VW_DATA.TEAM_ID = TABLEDATA.TEAMS.TEAM_ID
INNER JOIN
TABLEDATA.TEAM_TYPE ON TABLEDATA.TEAMS.TYPE_ID = TABLEDATA.TEAM_TYPE.TYPE_ID
WHERE (TABLEDATA.VW_DATA.DURATION > (SELECT MIN(duration) FROM TABLEDATA.VW_DATA WHERE type = 'Complete' AND team_id = 27))
AND (TABLEDATA.VW_DATA.DURATION < (SELECT MAX(duration) FROM TABLEDATA.VW_DATA WHERE type = 'Complete' AND team_id = 27))
GROUP BY TABLEDATA.VW_DATA.COMPLETIONDATE,
TABLEDATA.VW_DATA.TYPE,
TABLEDATA.VW_DATA.TEAM_ID,
TABLEDATA.VW_DATA.SITE,
TABLEDATA.MODELS.MODEL,
TABLEDATA.TEAMS.DURATION,
TABLEDATA.TEAM_TYPE.TYPE
HAVING (TABLEDATA.VW_DATA.TYPE = 'Complete')
AND (TABLEDATA.VW_DATA.TEAM_ID = 27)
AND (TABLEDATA.MODELS.MODEL <> 'Model1')
ORDER BY TABLEDATA.VW_DATA.COMPLETIONDATE DESC
----------------------------------------------------------------------------

This query results in the following:
Complete 27 88 SITEA MODELT 80 AGG 2004-05-20 00:00:00
Complete 27 83 SITEA MODELT 80 AGG 2004-05-07 00:00:00
Complete 27 70 SITEA MODELT 80 AGG 2004-05-01 00:00:00
Complete 27 110SITEA MODELT 80 AGG 2004-04-19 00:00:00
Complete 27 87 SITEA MODELT 80 AGG 2004-03-17 00:00:00
Complete 27 110SITEA MODELT 80 AGG 2004-02-27 00:00:00
Complete 27 73 SITEA MODELT 80 AGG 2004-02-26 00:00:00
Complete 27 115SITEA MODELT 80 AGG 2004-07-02 00:00:00

What I need is one row per site, and I can get that when I exclude the completiondate field, but I need to sort on that field. How can I use the GROUP BY to return only one row with the team_id, avgduration, site, model, duration, and teamtype. I do not need to display the completiondate, I just need it for sorting. The avgduration column is supposed to be an average of the duration of each of the records.

Thanks...

View 6 Replies


ADVERTISEMENT

SQL Server 2012 :: Obtaining A Comma Delimited List For Each Group In The Output Of A Group By Query?

Jan 10, 2014

I'd like to ask how you would get the OUTPUT below from the TABLE below:

TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B

OUTPUT:

category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6

The code would go something like:

Select category, count(*), .... as id's
from TABLE
group by category

I just need to find that .... part.

View 3 Replies View Related

Reporting Services :: Display Group Name Value Of Each Group In Column Header Outside The Group?

Sep 29, 2015

I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.

'GroupName' column has multiple values - X,Y,Z,......

I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...

Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName'  values:

Example:

Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z

ie the column name of ABC (Clm ABC)  must be dynamic as per the GroupName values (X,Y,Z....)

Page1:

GroupName                 Clm ABC-X

X

Page2:

GroupName                 Clm ABC-Y

Y

Page3:

GroupName                 Clm ABC-Z

Z

I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....

However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,

I get the following error:

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope

I need to get the X, Y, Z ... in each page for the column ABC.

I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...

View 4 Replies View Related

SQL Group By Query

Jul 26, 2007

I am back and need some help with Group By query. I am using SQL Server 2000. I have table in a database from which I have to SELECT some records. The first field is callingPartyNumber. I am trying to SELECT a few fields including callingPartyNumber, and SUM(duration). Is it possible to group the results according to the callingPartyNumber field only (knowing the fact that I have other fields also in the SELECT part of the query)?
The type of output I want is that all the records are grouped by the callingPartyNumber field and the sum of duration field for each groups is also returned.
 SELECT callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber,
dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 , '01-01-1970 00:00:00') AS dateTimeConnect,
dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600, '01-01-1970 00:00:00') AS dateTimeDisconnect,
CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration,
SUM(duration)

FROM Calls

GROUP BY callingPartyNumber

I cannot normalize the tables (implies that I have to work on one and only one table).Anyone...
 
 

View 25 Replies View Related

How Do I Group This Query Here

Nov 4, 2007

I have the following sql statement below;DECLARE @val intSET @val = 1WHILE (@val <= 7 )BEGIN SELECT TOP(1) id, queueFROM itn_articles WHERE asection = @valORDER BY queue DESCSET @val = @val + 1END Essential it just loops through a select statement 7 times, now the problem is how would I do this and group my results together so I could ORDER them; instead them coming out in a different query output and, which makes the order unrankable

View 2 Replies View Related

SQL Group By And SUM Query

May 9, 2008

Hi Guys,I have the following select sql (sql server) select statement SELECT
dbo.AlternativesAssessment.AlternativeNumber,
dbo.AlternativesAssessment.UserID,
dbo.AlternativesAssessment.MembershipValue,
dbo.AlternativesAssessment.CriteriaID,
dbo.CriteriaDefinition.CriteriaName,
dbo.CriteriaDefinition.DecisionSessionID

FROM
dbo.AlternativesAssessment INNER JOIN
dbo.CriteriaDefinition ON dbo.AlternativesAssessment.CriteriaID = dbo.CriteriaDefinition.CriteriaID

WHERE (dbo.CriteriaDefinition.SessionID = @SessionID)

ORDER BY dbo.AlternativesAssessment.UserID, dbo.AlternativesAssessment.AlternativeNumber and it produces a result like this:In this case there are 4 alternatives all evaluated through 3 criteria "c1_Cost", "c2_Durability" and "c3_Reputation".My objective is to sum up all the MembershipValue's for each alternative.it shoud look like this:AlternativeNumber|CriteriaName|MembershipValue|    1      Cost           2314    1      Durability        214    1      Reputation        981    2      Cost            814    2      Durability        914    2      Reputation        381.etcDo you guys have an idea how I could achieve that?I played around with SUM's and GROUP By's for hours now but it does not seem to work.Many thanks,polynaux

View 2 Replies View Related

Query Using Group By

Jan 11, 2001

I have a vendor table with vendor codes of six characters. The same vendor could have two codes (I'm new to this accounting system so I don't know why this is the case). If a vendor has two codes, the first character for one vendor code would be an "H", then the first character for the other code would be a "P", with the other 5 characters being the same (i.e. HCAMER and PCAMER).

We want to print only one 1099 for the vendor. So, if the vendor has two codes, I need to get a total for that vendor. Can this be done in a query?

Thanks.

View 1 Replies View Related

Max Query With Group By

Dec 18, 2006

Hi

I have a table in which column are id, date, lname, fname, add
In this table there are duplicate records but have different date for each duplicate record. I am trying to get just max(date) for each record.

when i try like these it works.
select id, max(date) from table group by id

but when i try something like these it does not work
select id, fname, lname, max(date) from table group by id, fname, lname

I get all the record.
I am writing these query from perl script. any help.

View 3 Replies View Related

Group By Query

Jun 22, 2004

Hi,

Please help in writing a SQL query.
I have a table with EmpId,DirectSales,TeamLeaderId,TeamMemSales as some of the columns.The situation is
a Team Manager as sell directly, which comes under directsales and the overriding value of his team member comes under teammemsales.

Now I want get sum of both for a particular manager on a daily basis. Like

SaleDate sum(Directsales) sum(TeammemSales)

if the given id is empid then the it is directsales and if it is in teamleaderid then it is teammemsales.

How to achieve this task in query?

Any help is appreciated.

Thanks

M.L.Srinvas

View 1 Replies View Related

Need Help With Group Query

Oct 3, 2005

Hi all,

I have 2 tables 1 is called Phonebrands the other is called phonetypes
is it possible to show all the phonetypes gouped by the phone brand?

Cheers Wim

View 5 Replies View Related

Group By Query

Oct 11, 2006

I have this table .....

AID NAME DATE AMOUNT TYPE
1001 ABC 1/1/2006 120 AC
1001 ABC 1/1/2006 23 AC

1001 BC 1/1/2006 12 AC
1001 DC 1/1/2006 22 TR

1002 ZX 1/1/2006 21 DR

1003 ABC 1/1/2006 23 AC
1003 VF 1/1/2006 44 AC


Now I want a query which will give a result set of - between a specific date
i.e between 1/1/2006- 1/2/2006


AID NAME AC_AMOUNT TR_AMOUNT DR_AMOUNT
1001 ABC 143 0 0
1001 BC 12 0 0
1001 DC 0 22 0
1002 ZX 0 0 21

----
1003 ABC 23 0 0

One row for each name,that is group up by will be on AID and NAME.

Any help will be greatly appreciated...

View 6 Replies View Related

Help With GROUP BY Query....

Jun 6, 2007

This is a fairly compliacted query and I just can't seem to figure it out.
I will try to psuedocode it to see if anyone can figure out how to do it with sql.

Table Descriptions:
I have to join two tables. Houses and Inpsections. A house can have many inspections. A single inspection can only be done on 1 house.
Houses: HouseID, HouseDescription
Inspections: InspectionID, Inspection_date, House_Id, pass_inspection ("yes"/"no"),

What I need:
I need to have only 1 result per House. For each house, I need the results for the LATEST INSPECTION. That is pass_inspection, houseID, inspection_date for the last time the house was inspected ONLY (that is if its been inspected at all - if not i still need the house listed and say "not inspected yet").

A query would be greatly appreciated.

Thanks,

DB

If you want to go further (which I will need to do) I need actually do this for each pair of House + problem. Houses and problems have a many to many relationship.

Problems: problem_id, problem_inspection
house_problems: problem_id, houseID

View 14 Replies View Related

Query Using Group By

Jul 11, 2013

I have a table that has picktime, route_id, stlat and stlong columns.There might be multiple records having same picktime and routeid.

Select S.pickup_time, S.route_id from STARTTBL as S where Start_Dt= '7/11/2013' group by S.Route_id

(pktime) route_id stlat stlat
08:50 17 -32.01 102.98
09:50 17 -33.01 102.98
14:25 25
16:30 25
16:00 29

1.Is it possible to get the all the 4 columns where pickup_time is MAX for each Route_id

For the above data , the resulting data would be

09:50 17 -33.01 102.98
16:30 25
16:00 29

View 3 Replies View Related

Group By Query Help

Feb 28, 2006

Can you group by a value returned by a UDF? Example:SELECT Col1, SUM(Col2), MyFunc(Col3, Col4) AS 'MyResult'GROUP BY Col1, MyResultI've had no luck. Can this be done?

View 4 Replies View Related

DMX Query, Group By

Nov 14, 2006

I'm having some problem with this DMX prediction query. This is the first time I'm trying out the GROUP BY statement in the DMX query and I keep getting "Parse: the statement dialect could not be resolved due to ambiguity." message.

Is Group By supported by the DMX? What am I missing? If not supported, could I insert the result into a temporary table using SELECT ... INTO.. FROM and run a group by on a temporary table?

This is what the DMX query looks like...

SELECT
t.[AgeGroupName],
t.[ChildrenStatusName],
t.[EducationName],
Sum(t.[Profit]) as Profit
From
[Revenue Estimate DT]
PREDICTION JOIN
OPENQUERY([DM Reports DM],
'SELECT
[AgeGroupName],
[ChildrenStatusName],
[EducationName],
[Profit],
[IncomeName],
[HomeOwnerName],
[SexName],
[Country],
[ProductTypeCode],
[ProductName],
[MailCount],
[OrderAmount],
[SalesAmount],
[MailCost]
FROM
(SELECT AgeGroupName, ChildrenStatusName, EducationName, IncomeName, HomeOwnerName, MaritalStatusName, SexName, JobName, JobTypeCode,
CompanyTypeCode, Country, ProductTypeCode, ProductName, SUM(MailCount) AS MailCount, SUM(OrderAmount) AS OrderAmount, SUM(SalesAmount)
AS SalesAmount, SUM(MailCost) AS MailCost, SUM(Profit) AS Profit, MIN(RevenueEstimateID) AS ReKey
FROM [DataMining.RevenueEstimate.Predict]
GROUP BY AgeGroupName, ChildrenStatusName, EducationName, IncomeName, HomeOwnerName, MaritalStatusName, SexName, JobName, JobTypeCode,
CompanyTypeCode, Country, ProductTypeCode, ProductName, ClientID
HAVING (ClientID = 1)) as [Prediction]
') AS t
ON
[Revenue Estimate DT].[Age Group Name] = t.[AgeGroupName] AND
[Revenue Estimate DT].[Education Name] = t.[EducationName] AND
[Revenue Estimate DT].[Income Name] = t.[IncomeName] AND
[Revenue Estimate DT].[Home Owner Name] = t.[HomeOwnerName] AND
[Revenue Estimate DT].[Sex Name] = t.[SexName] AND
[Revenue Estimate DT].[Country] = t.[Country] AND
[Revenue Estimate DT].[Product Type Code] = t.[ProductTypeCode] AND
[Revenue Estimate DT].[Product Name] = t.[ProductName] AND
[Revenue Estimate DT].[Mail Count] = t.[MailCount] AND
[Revenue Estimate DT].[Order Amount] = t.[OrderAmount] AND
[Revenue Estimate DT].[Sales Amount] = t.[SalesAmount] AND
[Revenue Estimate DT].[Mail Cost] = t.[MailCost] AND
[Revenue Estimate DT].[Profit] = t.[Profit] AND
[Revenue Estimate DT].[Children Status Name] = t.[ChildrenStatusName]
GROUP BY t.[AgeGroupName],
t.[ChildrenStatusName],
t.[EducationName]

View 3 Replies View Related

Select Query By Group

Jun 20, 2008

Hello All
I have table with 3 columns  and Datas like this
LRNo              LRAmount   LRType
L001                100                TP
L002                250                PA
L003                150                AC
L004                275                TP
L005                400                PA
L006                125                TP
 
I need a query to get output like this
 
LRtype      Count           TotalAmount 
TP               3                    500
PA               2                   650
AC              1                   150
 
Where count is total no  of records grouped for LR Type and totalAmount is Sum of TotalAmount grouped by LRType
 
please help me
Thanks in Advance
 
 

View 4 Replies View Related

Select Top N, Group By Query

Nov 14, 2005

Hi All,

I am trying to return a result set that gives me the top 20 results for EACH group (PERILNAME in this case), not the top 20 results of the whole result set.

I have been able to create the resluts I want using UNION, but this is not practical when there is more than a few groups. Below is the code the returns the results I am after, any ideas how to achieve this another way?

SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT) AS [Total Value],
COUNT(loc.LOCID) AS [Num Locs],loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Earthquake'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Windstorm'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Tornado/Hail'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Flood'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
ORDER BY LookupPeril.PERILNAME, [Total Value]

TIA.

View 4 Replies View Related

Select Top N, Group By Query

Nov 16, 2005

Hi All,

I am trying to return a result set that gives me the top 20 results for EACH group (PERILNAME in this case), not the top 20 results of the whole result set.

I have been able to create the results I want using UNION, but this is not practical when there is more than a few groups. I think it should be possible using a derived table, but I am not sure how to do it! Below is the code the returns the results I am after, any ideas how to achieve this another way?

SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT) AS [Total Value],
COUNT(loc.LOCID) AS [Num Locs],loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Earthquake'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Windstorm'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Tornado/Hail'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Flood'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
ORDER BY LookupPeril.PERILNAME, [Total Value]

TIA.

View 3 Replies View Related

More Complex GROUP BY Query

Jan 18, 2006

Hi, I am not a newbie to general SQL queries but i do get confused with the complicated ones.
I have a table of applicants. I want to be able to count applications over the course of a month but group them by week. does that make sense?
So essentially i would have 4 records for the query: week 1's total, week 2's total. etc..
Is this possible in one query? or do I need to create a query for each week with the date range i need?

Thank you for any help you can give!

Nate

View 4 Replies View Related

'group By' And 'substrings' Query Help

Jan 5, 2007

i have a table like this:


Code:

Product
id - varchar(10)
description - text



the ids are something like this
mod01-01
mod02-01
mod03-01
mod01-02
mod02-02
mod03-02
mod03-03
mod03-04
mod04-01
....

now, i am trying to group by and count all of the ids that end with '01',

i was thinking of something like
select count(id)
group by id
where id= ???

and the second query i need to make is one that counts not one but all the ids that end with the same last 2 digits but in one single query, how can i do that? as a possible hint, is that the only thing i KNOW that the last 2 digits can only be 01,02,03,04 and 05 but still im a bit stuck in here the result should look something like this

id total
01 4
02 3
03 1
04 1

or what suggestions can you give me for this query? even if you just help me with the first query, that would be great, thanks!!
thanks!!!

View 6 Replies View Related

Help With Count And Group By Query Pls!!

Dec 6, 2006

ok, so i have this table "SOLD_PRODUCTS" with this columns:
idProduct (int), quantitySold (int)

so i want to know how many products i have sold.

i have this query

Code:

SELECT idProduct, COUNT(idProduct) AS Total
FROM SOLD_PRODUCTS
GROUP BY idProduct


and get this
74 5
75 2
79 1
etc etc etc

this works correctly, but it doesnt counts the quantity sold, so i changed the query to


Code:

SELECT idProduct, quantitySold, COUNT(idProduct) AS Total
FROM SOLD_PRODUCTS
GROUP BY idProduct


but it crashes, mssql says i must add quantitySold to the group by clause.

but if i put the query

Code:

SELECT idProduct, quantitySold, COUNT(idProduct) AS Total
FROM SOLD_PRODUCTS
GROUP BY idProduct,quantitySold



i dont get the expected data, the idProducts appear more than once, they are no longer grouped by, the results in the count(idProduct) are wrong (i am not sure what they are counting now)

so now i dont understand how i can make my query, all i want is a query where in one collumn i have the product id, and in the other the quantity sold (that would be the count of all the product ids found multiplied by the quantity sold)

so now i am completelly lost, now i dont know what to do with my query.

any help pls pls pls!!!!

View 1 Replies View Related

Select Top 3 By Group Query

Jun 20, 2006

Hi

I need some help in returning the top 3 records in each group ie I have a table containing a list of stores with the address details and I need to return the top 3 records in each postcode area. The Postcode field contains the postcode area ie BN1, BN2 etc. Many thanks.

View 3 Replies View Related

Sum, Count And Group By In The Same Query

Apr 16, 2008

Hi,
I have two tables in my DB:
tbl_Users: callSign(Char), FirstName(Char), LastName(Char)
tbl_Events: CallSign(Char), TotalKM(Char), EventDate(SmallDateTime)... Plenty of others, but they're not relevnt.

The result that I want to see is:

CALL SIGN Last Name First Name Date Number Of Events TotalKM
111 MR. X 01/01/2008 3 40

I know this:
The number of events is countable.
if you do the following convert "Convert(int, TotalKM) you can sum up the Total KM.

But how do I group it together?!

Thanks,
Gil

View 2 Replies View Related

Query With Count And Group By

Nov 19, 2013

I have a table of people:

Name, City, Sex

I want a table where each line is the city, and the number of Males and Females.

For example, this is what I tried, and I know it's wrong:

Select City, count(name) where Sex = 'M' as NumberM,
count(name) where Sex = 'F' as NumberF
Group by City

How do I do this?

View 3 Replies View Related

Get First Record From Group By Query

May 30, 2006

Hendra writes "I'd like to know is there any way or any statement that can help me to get first or last record from the group by query ?
I'm using sql Server 2000
Thank's

_Hendra_"

View 1 Replies View Related

Group By Clause Query Help

Jul 23, 2005

This is my queryselect ano,max(date),a_subject from MY_TAB where table_name='xyz' andano=877group by a_subject,ano order by a_subjectANOmax(Date)A_Subject8772005-01-20 00:00:00.000Subject_18771900-01-01 00:00:00.000Subject_28772004-12-20 00:00:00.000Subject_38772005-01-19 00:00:00.000Subject_4--------------------------------------------------------------------------When I put the status column in, it fetches all the rows.select ano,max(date),a_subject,status from MY_TAB wheretable_name='xyz' and ano=877 group by a_subject,ano,status order bya_subjectANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8771900-01-01 00:00:00.000Subject_3Not Started8771900-01-01 00:00:00.000Subject_4Not Started8772005-01-19 00:00:00.000Subject_4Not Started-----------------------------------------------------------------------now what i want isANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8772005-01-19 00:00:00.000Subject_4Not StartedThanks a lot for your help.AJ

View 2 Replies View Related

2 Group By In A Single Query

Jul 20, 2005

Say the following are the columns of a tableA B C D E FCan a aggregate function like sum be applied to A like sum(a) and thenorder by b and c similarly aggregate function on d and group by e andf using a single query...Regards,Jai

View 2 Replies View Related

How To Group The Retuned Query!

Aug 13, 2007

Hello,

I am wondering how can I group the returned results from the sql query. For example, the following sql qurey retrieved data from a few tables from the database on conditions using "inner join". The returned results are not grouped or organized into groups. I would like to group the retrieved data based on three different tables such as "Overall", "Spectography" and "Other" using the CASE statement.


psql += "select s.szID as SampleID, rtp.nSequence as Sequence, rt.szName as TestType, ";

psql += "rtp.szName as TestName, p.szID as PointTest, p.szDescription as PointDescription, ";

psql += "r.nAlarmStatus as AlarmStatus, s.dtCollected as DateCollected, ";

psql += "rd.nDataType as DataType, rd.byData as EncodedData ";

psql += "from point p ";

psql += "inner join readtype rt on (rt.gPK = p.gReadType) ";

psql += "inner join reading r on (p.gPK = r.gPoint) ";

psql += "inner join sample s on (s.gPK = r.gSample) ";

psql += "inner join readdata rd on (rd.gReading = r.gPK) ";

psql += "inner join rtparam rtp on (rd.gRTParam = rtp.gPK) ";

psql += "where s.szID in (" + sampleList + ") ";

psql += "and rtp.szName not like '%_Sev' ";

Any suggestions would be very much appreciated.

Thanks

View 6 Replies View Related

Trouble Using Group By Statement With Query

Mar 20, 2007

Hi,
I am having trouble getting my query right. i am using a stored procedure to retrieve this data and bind it to my grid view.
Problem: I can't associate a field with a column that i am returning with my record set.
Details: I have query that i want to return 9 columns (UserName, Pre-Approved, Processing, Underwriting, Conditioned, Approved, Docs Out, Docs Back, Conditions Met). The username column lists all loan agents. I want the other 8 columns to list the name of the borrower (crestline..borrower.lastname)  that is associate with that loan agent and that loan state.  Each time a record is found where there is a valid loan agent (UserName) that meets the 'where' conditions, there will be a borrower. the 'LoanKey' field is a primary key that lets me get the borrower name from the borrower table. I dont know how to construct my query such that those borrower names get associated with their respective column header.
if the query works, my Gridview should look like this ('Name' refers to the borrower name)
UserName | Pre-Approved | Processing | UnderWriting | Conditioned | Approved | Docs Out | Docs Back | Conditions Met 
Bob           |                     |                  |                     |    Name      |                |               |                   |
Bob           |                     |      Name   |                     |                   |                |               |                   |      
Bob           |                     |                  |                     |                   |                |               |      Name    |        
Steve         |                     |                  |      Name      |                   |                |               |                   | 
Steve         |                     |                  |                     |                   |                |    Name  |                   |
 
Here is my sql call: 
SELECT cfcdb..users.username, crestline..borrower.lastname,CASE WHEN crestline..loansp.LoanStatus='Pre-Approved' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Pre-Approved',CASE WHEN crestline..loansp.LoanStatus='Processing' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Processing',CASE WHEN crestline..loansp.LoanStatus='Underwriting' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Underwriting',CASE WHEN crestline..loansp.LoanStatus='Conditioned' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Conditioned',CASE WHEN crestline..loansp.LoanStatus='Approved' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Approved',CASE WHEN crestline..loansp.LoanStatus='Docs Out' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Docs Out',CASE WHEN crestline..loansp.LoanStatus='Docs Back' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Docs Back',CASE WHEN crestline..loansp.LoanStatus='Conditions Met' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Conditions Met'FROM cfcdb..usersinner join (crestline..loansp inner join crestline..borrower on crestline..loansp.loankey = crestline..borrower.loankey)on crestline..loansp.fstnamelo=cfcdb..users.firstname AND crestline..loansp.lstnamelo=cfcdb..users.lastnameinner join cfcdb..users_roleson cfcdb..users.username = cfcdb..users_roles.usernamewhere cfcdb..users.active = 1 AND cfcdb..users_roles.groupid = 'agent'AND crestline..loansp.enloanstat <> 'Closed' AND crestline..loansp.enloanstat <> 'Cancelled' AND crestline..loansp.enloanstat <> 'Declined' AND crestline..loansp.enloanstat <> 'On Hold'order by cfcdb..users.username asc
 

View 2 Replies View Related

SQL Query Problem In Group By Clause

Jan 26, 2004

I am developing Staff Allocation System,
database is sql server 2000.
I have problem in retrieve the staff informations,

employee working which Project and what project have assign to him, what is his assign project or contract no,

One employee working more then one project, retrieve information one employee how many projects are working,

What is his approved position, what is his assign position.
It the main data have to retrieve, as well as retrieve all fields which related to those tables.

I use this query.

select name,apppos approved_position,appcont approved_contract,appdate employee_appr_date,munref Municipality_Ref,dcilref DCIL_REF,projtype Project_Type,strdate Project_str_date,comdate Projcet_comp_date,extdate Proejct_ext_date,dept,emptype Employee_Type from contract,emp,apprecords where contract.rec_id=emp.rec_id and emp.rec_id=apprecords.rec_id and apprecords.name='dewachi'


above query retrieve no data,

how can use group by clause in the above query ?

group by apprecords.appcontract

group by clause give error.

above query have to retrieve data from the three tables, I have four tables, what query I use so that all four tables data retrieve like this.


Name, approved_position, approved_contract,assign_position,assign_contract,startdate,completion_date,........ and so on…
Group by apprecords.appposition
……….


Contract Table (basic data entry contract table)
-------------------------------------------------------
rec_id
Contract No.
ProjectType
StartDate
CompletionDate
ExtendedDate


Employee Table (basic data entry employee table)
---------------------------------------------------------
rec_id
EmpNo
Name
Position
Department
EmployeeType


Approved Records Table (in this table all information about
the employee and his approved
position and contract )
------------------------------------------------------------------------
rec_id
Name
Approved Date
MunicipalityRefNo
DCILRefNo
ApprovedPosition
ApprovedContract


Assign Project Table (in this table all information about the
employee his assign the project)
--------------------------------------------------------------------
rec_id
Name
AssignPosition
AssignContract
EmpProjectStartDate
EmpProjectEndDate
ShiftNo
ProjectStatus



Regards.


MATEEN

View 6 Replies View Related

How To Write A Nested GROUP BY Query, Please Help.

Jun 6, 2004

Use Pubs
SELECT pub_id, type, SUM(price) as Total_price
FROM titles
GROUP BY pub_id, type

The above query returns the following resultset:


0736business 2.9900
1389business 51.9300
0877mod_cook 22.9800
1389popular_comp42.9500
0736psychology 45.9300
0877psychology 21.5900
0877trad_cook 47.8900
0877UNDECIDED NULL


Now I want to add another "Group By" on type, so I tried:

Select type, sum(Total_Price) from
(SELECT pub_id, type, SUM(price) as Total_Price
FROM titles
GROUP BY pub_id, type)
Group By type

But I got error: Incorrect syntax near the keyword 'Group'. How can I write such a nested group by query.

Thanks in advance for any help.

View 5 Replies View Related

GROUP By Clause, /sub Query Problems

Aug 15, 2005

I'm trying to list salesreps (if they have any sales for a particular date) with their total sales amounts for a queried date, but when running this sql string in QueryAnalyzer, it says there is an error with syntax on Line 1 near "s" :SELECT o .Rep_ID, o .ID, s.ID, SUM(b.orderamount) AS totalsales, b.order_ID
FROM (SELECT b.Deal_ID
FROM btransactions b
WHERE b.BoardDate = '20050815') SalesReps s INNER JOIN
orders o ON o .Rep_ID = s.ID INNER JOIN
b ON o.ID = b.Deal_ID
GROUP BY d .Rep_ID, d .ID, s.ID, b.order_ID
HAVING (SUM(b.orderamount) > 0)???.NetSports

View 1 Replies View Related

Using Group By In Query With Three Columns Table

Oct 21, 2005

Hi,I guess my brain stopped but I need help with this. create table #RETURN(  uid int,  cid int,  serviceid int  )
 insert into #RETURN select 44,75,2 insert into #RETURN select 44,76,1 insert into #RETURN select 44,77,3 insert into #RETURN select 45,78,3I need to query this table that will get me a distinct UID with matching  CID  ordered by serviceid from lowest to highest.The solution should look likeuid   cid44   7645   78Thanks

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved