How To Query By Grouping One Column
Jan 5, 2005Hello, everyone:
I have a table like:
Col1Col2
1A
2B
1D
1P
2F
2W
How to query this table to return by Col1 like
Col1Col2
1A,D,P
2B,F,W
Thanks a lot
ZYT
ADVERTISEMENT
Query Or Grouping Problem (some Kind Of Parallel Grouping?)
Nov 26, 2007I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.
I'm trying to get information like this in to a report:
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc
I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?
There are 4 tables:
Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions
The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:
WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc
Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR
It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:
SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]
Counting Multiple Values From The Same Column And Grouping By A Another Column
Sep 16, 2004This is a report I'm trying to build in SQL Reporting Services. I can do it in a hacky way adding two data sets and showing two tables, but I'm sure there is a better way.
TheTable
Order# Customer Status
STATUS has valid values of PROCESSED and INPROGRESS
The query I'm trying to build is Count of Processed and INProgress orders for a given Customer.
I can get them one at a time with something like this in two different datasets and showing two tables, but how do I achieve the same in one query?
Select Customer, Count (*) As Status1
FROM TheTable
Where (Status = N'Shipped')
Group By Customer
Multi Column Grouping Vs Page/Column Break
Mar 18, 2007In my report i would have 2 groups.
The first group should cause a real page break, the secound group should cause a column break.
Any idea on how to realize this, i've been playing with quite some settings but .....
So, any help ...
Grouping And Sum Column
Oct 9, 2007Hi,
I have table and i need to sum one colume of this table and group by not all column table but with some of the table column for instance.
Table is:
SerialNum | Factry | DateTime | Owner,QTY | Cost | Description
I need to sum Qty and group by Serical number and owner only.
How can i do that?
Thank you,
Sms
Grouping By A Formated Column
May 13, 2008I have a date column that formated and i need to group by it, how do it do this? i get this error code:
Msg 8120, Level 16, State 1, Line 2
Column 'o.Initials' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
here is my query:
select * from(
SELECT CONVERT(VARCHAR(10), AITDate, 110) AS FormatDate
, TechInt as Initials
, [Drug]
,[Missed PT Notes]
,[Pt Same Name]
,[PT Family]
,[Wrong PT]
,[Wrong Ques. Asked]
,[DAW]
,[Rx/Refills Omitted]
,[Allergy]
,[Wrong Sig]
,[Strength]
,[Wrong PT Address]
,[Quantity]
,[SVQ]
,[Refills]
,[Write Date]
,[Wrong MD/MD Info]
,[Rx Should be Questioned]
,[TSTF],[Non-Link Image]
,[DC Date],[Days Supply]
,[No Call Card]
,[Order Count]
,[Credit Hold Procedure]
,[Routing]
,[Sig Typo]
,[Verse Missing/Wrong]
,[Right Drug Wrong Form]
,[Others]
from tblaitinfo
pivot (count(ercode) for ercode in ([Drug],[Missed PT Notes],[Pt Same Name],[PT Family],[Wrong PT],[Wrong Ques. Asked],[DAW],[Rx/Refills Omitted],[Allergy],[Wrong Sig],[Strength],[Wrong PT Address],[Quantity],[SVQ],[Refills],[Write Date],[Wrong MD/MD Info],[Rx Should be Questioned],[TSTF],[Non-Link Image],[DC Date],[Days Supply],[No Call Card],[Order Count],[Credit Hold Procedure],[Routing],[Sig Typo],[Verse Missing/Wrong],[Right Drug Wrong Form],[Others])) p
)o
group by formatdate
Grouping Rows By CSV Column
Aug 24, 2013I have a table with following sample data:
**CategoriesIDs RegistrantID**
47 1
276|275|278|274|277 4
276|275|278|274|277 16261
NULL 16262
NULL 16264
NULL 16265
[code]....
I need to know :
1). which category has how many regisrtantids (like 277 has how many registrantids) 2). group the registrants by category so that i can find which registrants are in category 277 for example)
Do I need to create a function which generates a table from csv ? I have created a function but not sure if it will work in this situation with IN clause.
Matrix Column Grouping
Sep 15, 2006Hi,
I have a requirement from my users to be able to drill down for a single column in a matrix. I've been able to implement drilldown for all of the measures (all columns grouped at the same time) in my matrix but they now want to be able to group different columns independently of each other. In other words they want to be able to group the data in different columns by different things.
An example might make my question clearer, the report would need to look like this:
Client Name
Sales -
Costs +
Revenue -
Client A +
Europe +
Middle East +
Asia Pacific +
$12,000,000
Products +
Investments +
$12,000,000
$8,000,000
$4,000,000
$10,000,000
$22,000,000
Client B +
$77,000,000
$16,000,000
$9,000,000
$22,000,000
$8,000,000
$32,000,000
As you an see, the Sales measure has been expanded so it's grouped by region so the sales figures can be seen for all clients and the 3 regions. The revenue column has also been expanded/grouped so that you can see revenue figures for products and investments for all clients. Costs is not expanded but it could be by clicking on the '+' which would group the data in that column by something else. They have asked for more than 1 level of drilldown, so clicking on "Europe" above would allow another level for the sales measure which might be country. There will also be regular measure columns that are not drilldown/groupable columns.
Is it possible to implement this with SSRS 2005?
Thanks,
Lachlan
Grouping Column Currency By Range
Apr 11, 2007HI all -
I need to group data in a sql report by currency values. I have a report that shows part numbers and 4 columns making up manufacturing costs and then a 5th column showing total costs. I would like to add a 6th column that groups the total costs by ranges. The example would be, group all costs > 20000, group all costs 1000 > x > 20000 , and finally all costs < 1000.
How can I accomplish this?
Transact SQL :: Grouping Based Of Column Value
Nov 5, 2015Below is the data I have in table name
TeamStatus
T 1 Complete or Escalate
T 2 Pick Up
T 2 Resolve Case
T 1 Pick Up
T 1 Complete or Escalate
T 1 Pick Up
T 1 Complete or Escalate
I want to get he group based of Resolve Case value in Status Column. Anything before Resolve case will be considered as Group 1 and after Resolve Case status should be considered as Group 2. Below is desired new Group column,
Group TeamStatus
Group 1 T 1Complete or Escalate
T 2 Pick Up
T 2 Resolve Case
Group 2 T 1Pick Up
T 1Complete or Escalate
T 1 Pick Up
T 1 Complete or Escalate
T-SQL (SS2K8) :: Dividing Two Values Of Same Column / Grouping
Aug 4, 2014I'm trying to divide two values from separate rows. Each row is a separate UNION statement.
2014-08-03 00:00:00.000NKBB (N) - Total Offers 1218 UNION (A)
2014-08-03 00:00:00.000NKBB (N) - With Lead 301 UNION (B)
2014-08-03 00:00:00.000NKBB (N) - Without Leads 917 UNION (C)
In the below example, I would like to divide KBB (N) - With Lead (UNION (B)/KBB (N) - Total Offers UNION (A)
What would be the best way to accomplish this?
Reporting Services :: Column Header Over Grouping
Aug 11, 2015I would like to create a group and add an overall header.
So the dimension is grouped and results in the following:
Group Title
Dimension 1 Dimension 1 Dimension 1 Dimension 1
Instead of :
Group Title Group Title Group Title Group Title
Dimension Dimension Dimension Dimension
How can I achieve this without using a tablix within a tablix?
Reporting Services :: Reference Column Grouping
Aug 31, 2015I create a matrix,
Row groups: Province
Column groups: Project Type
Values: Count of # of Projects, Sum of Amount ($)
What this displays is the following. How do I go about calculating what's highlighted in yellow? It is Amount of OneProjectType / Amount of TwoProject Type.
Reporting Services :: Customize Column Grouping
Jul 6, 2015I am working on one of the report. where I need to move the amount across column group.
My data is like this
Project Amount CurrentFiscalMonth DelaybyMonth
A 10 Jul 0
B 20 Aug 2
C 10 Sep 0
Report will be sum up the amount at CurrentFicalMonth grouping on column
But, if the Project is being delayed say by as per the example its 2 months than the amount should be moved from Sep to Oct column for the particular project. User has done it using the Offset function in excel.
Fiscal month starts at July and ends at June. and data source is SSAS cube.
Joining On And Grouping By CASE Function Column Alias (URGENT)
Apr 14, 2004I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet:
SELECT NewColumn=
CASE
WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO'
END,
Table2.SelectCol2
FROM Table1
JOIN Table2 ON NewColumn = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2
I really appreciate any help anyone can provide.
Thanks,
DC Ross
Reporting Services :: Tabular Report Datasets - Grouping On Column
Jun 23, 2015I have table which has 5 columns(col1,col2,col3,col4,col5) very simple and some data .
I would like to create a tabular report datasets on the report like shown below with grouping on col1
like
col1 some static text and dynamic text
------------------------
col1 col2 col3 col4
data data data data
------------------------
col1 some static & dynamic text
------------------------
col1 col2 col3 col4
data data data data
------------------------
Note I need to keep all the tables on the same page and when exported in excel they should come on same page sheet.
SQL Query - Need Help With Grouping
Oct 22, 2007Hi,
I have this query...
cmd = New SqlCommand("SELECT name, webd_category_desc.category_id, (name + cast(webd_category_desc.category_id as nvarchar)) as CNameID, link_id FROM webd_category_desc left outer join webd_link_category on webd_category_desc.category_id = webd_link_category.category_id where display = 'True' order by CNameID, link_id ;", SqlConnection1)
It produces the following output (trunctated by me for this post example).
name
category_id
CNameID
link_id
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
7
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
22
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
24
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
40
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
45
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
89
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
134
Accounting/Bookkeeping
2
Accounting/Bookkeeping2
137
Architecture
5
Architecture5
37
Architecture
5
Architecture5
90
I would like it to display instead (where 8 and 2 are the counts):
Accounting/Bookkeeping 8
Architecture 2
Seeing that I had to join a few tables to get the above output, how can I now group on it to get the name, count(name) output I desire.I'm using ADO.NET in a VB.NET/ASP.NET 2.0 webapp. The data is in SQL Server 2000. I was hoping to do it in one SqlCommand statement if possible. I guess I can drop it into a view and then run my group by query on the view if I had to.
I am getting a variety of 'field in select list must be used in a function or aggregate' errors in the attempts I have tried so far.
Thanks in advance,
Stewart
Grouping With SQL Query
Oct 29, 2007Hi folks. Hopefully this is a simple question. What's the easiest and most efficient way to group by a dateTime field in an SQL query? Here is exactly what I'm trying to do. I have a database table that contains transactions from an email maillog, so there are dateTime entries every second or so. I'm trying to build a query that will group a count of messages per hour for a given day. How can I make an hourly grouping of the total number of messages?SELECT count(*)
FROM emailTable
WHERE (delivDate >= '2007-10-03 00:00' AND delivDate < '2007-10-03' 01:00)
Thanks, Chris
Grouping By Sub Query
Jan 3, 2007is grouping by sub query possible?
ie.
Code:
select
(select fieldx from tabley where pk = tz.fk) as field1,
field2
from
tablez tz
group by
field1
this doesn't work..i get an error that field1 is not valid...so is there a way to do this that does work?
please realize that the above example is exactly that..and had i needed to do something that easy, join would be the easy choice..what i'm trying to do requires a sub query
Grouping (sum) Query
Jan 15, 2004I have the following information in a table
ACCNORundateTRDCAPTRANQTYDLPRCE NOTEAMNT
27547920031202A-170002150000
27547920031202A-27412150000
27547920031202A-259215000-42501729
I need to create a query that totals TRANQTY and arrives at a result as in the following record.
ACCNORundateTRDCAPTRANQTYDLPRCE NOTEAMNT
27547920031202A-20000215000-42501729
and now for the $1M question...How ? I've tried the following select, but it is not working the way I want it to..
SELECT c2.ACCNO, c2.Rundate, c2.TrdCap, c2.TRANQTY, c2.DLPRCE, c2.NOTEAMNT
FROM CLIENTSHAREDEALS c2 FULL OUTER JOIN
(SELECT c1.ACCNO, c1.SHARENAME, SUM(c1.TRANQTY) AS Expr1
FROM CLIENTSHAREDEALS c1
WHERE (c1.ACCNO = '275479')
GROUP BY c1.ACCNO, c1.RUNDATE, c1.SHARENAME) c1 ON c1.ACCNO = c2.ACCNO AND c1.RUNDATE = c2.RUNDATE
WHERE (c2.ACCNO = '275479')
ORDER BY c1.RUNDATE
Thanks
Help With Grouping Query...
Dec 5, 2005I have a table with the following structure:main_category| category| sub_category| answer|dateBasically, the data will be along these lines:Neuro | LOC | Status | answer1|dateNeuro | LOC | Status | answer2|dateNeuro | LOC | Status | answer3|dateSenso| Visi | Clarity | answer1|dateSenso| Visi | Clarity | answer2|dateetc...I am trying to query the db and present the user with the data in thefollowing structure:Main CategoryCategorySub Categoryanswer1answer2answer3...Main CategoryCategoryEtc...There are literally 3 dozen main categories, categories, andsub-categories each with distinct answers.I could really use some help on a query to group the data in this way!Thanks in advance!!!Frank
View 4 Replies View RelatedReporting Services :: SSRS 2012 - Page Break With Column Grouping
Nov 16, 2015We are facing problem in doing page break with column grouping. Our column group contains years e.g 2011, 2013 . We want to show a complete page for a year.
Suppose 2011 has 10 records(horizontal) and 2013 has 12 records(horizontal) in column. The output should be 10 records of 2011 in first page, 12 records of 2013 in second page.
We cannot change the report layout to make column to row and vice versa.
Can This Sub Query Code Can Be Done In Grouping
Feb 26, 2008HI all, I got a tsql that needs to be simplified.
Select * from Table1 where condition1 and id not in
( Select id from table1 where condition2 and id in
( Select id from Table1 where condition1 )
)
basicly all records thats in condition1 but that doesnt have condition2 but limited to condition1. I'm probably maken this to complicated. but im tired and im losing time just on one stupid query. Thanks for the help.
Min/Maximum Grouping Query
Jun 13, 2004I know this has been posted before, but I can't find the previous threads so please bear with me....
I want to grab the very 1st record of each product in a table like this
ID CLIENTID PRODID
1 a 1
2 b 1
3 c 1
4 a 2
5 b 2
6 c 2
7 a 3
8 b 3
9 c 3
so that I'd get a record set like:
ID CLIENTID PRODID
1 a 1
4 a 2
7 a 3
Thanks for the hellp guru's
Query For Grouping And Join?
Aug 22, 2014I have below data in one of my table:
Calls|Status
4|-6
12|-11
1|0
1|-10
Desired Output required :
Total_calls|Zero|Non-Zero
18|1|17
Grouping Select Query
Nov 8, 2005Hi,I have data stored as in below sample :-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-14 00:00:0063.00002005-11-14 00:00:002005-11-15 00:00:0063.00002005-11-15 00:00:002005-11-16 00:00:0045.00002005-11-16 00:00:002005-11-17 00:00:0045.00002005-11-17 00:00:002005-11-18 00:00:0045.00002005-11-18 00:00:002005-11-19 00:00:0045.00002005-11-19 00:00:002005-11-20 00:00:0045.00002005-11-20 00:00:002005-11-21 00:00:0063.00002005-11-21 00:00:002005-11-22 00:00:0063.0000-------------------------------+---------------------------------+--------------I have to group the select query in this way :-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-15 00:00:0063.00002005-11-15 00:00:002005-11-20 00:00:0045.00002005-11-20 00:00:002005-11-22 00:00:0063.0000-------------------------------+---------------------------------+--------------When I run below grouped statement, I get follewed result:SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,Rate FROM X GROUP BY Rate-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-22 00:00:0063.00002005-11-15 00:00:002005-11-20 00:00:0045.0000-------------------------------+---------------------------------+--------------How can I do a query like in 2nd sample from top?best regards,rustam bogubaev
View 2 Replies View RelatedTricky Grouping Query
Jul 20, 2005I'm having much difficulty figuring out how to write the followingquery. Please help!I have this table:EventEventId int Primary KeyPatientId intSeverityLevel intWhat I want returned in my query is a list of all (distinct)PatientIds appearing in Event, with the *most severe* EventId returnedfor each Patient. The higher the value of SeverityLevel, the moresevere that Event is considered to be.The problem I am having is that I can't figure out how to (a) group byPatientId, AND (b) return the EventId of the highest-severity Eventfor *each* PatientId (Order By SeverityLevel Desc).So if my table contained:EventId PatientId SeverityLevel------- --------- -------------1 1 02 1 13 1 54 2 55 2 2I would want my result set to be:PatientId EventId--------- -------1 32 4since events 3 and 4 are the most severe events for patients 1 and 2,respectively.Any help would be greatly appreciated. This seems to be something thatcould be handled easily with a FIRST() aggregate operator (as in MSAccess) but this is apparently lacking in SQL Server. Also note theremay be multiple Events with a given PatientId and SeverityLevel, inthat case I'd want only one of the EventIds (the Max() one).Many thanks,Joel ThorntonDeveloper, Total Living Choices<joelt@tlchoices.com>(206) 709-2801 x24
View 7 Replies View RelatedGrouping A Query In 30 Seconds
Apr 13, 2006Hi,
How can I make a query and group the registries in a interval of 30 seconds...like
for each line I have a datetime field that have all the day, and I need it to return just like
TIME Contador_type1 Contador_type2 Total
01-01-2006 00:00:30.000 2 5 7
01-01-2006 00:01:00.000 3 7 10
It's just an example...but that's the result that I need and my table is
data_hora -- datetime field
tipo - 1 or 2 -- count
nrtelefone - that's is the number dialed.
Thanks
SQL Server Grouping Query
Dec 12, 2007Hi,
Below is my DB Table..
Owner varchar(500)
contains...
OwnerA
OwnerB
Book varchar(500)
contains values...
Book1
Book2
Book3
Book1 might be owned by OwnerA
and Book2 might be owned by OwnerA and OwnerB
So in the Table is this...
ID Book Owner
1 Book1 OwnerA
2 Book2 OwnerA
3 Book2 OwnerB
How would I output this relationship in sql?
Thanks.
Need Help With A Simple Query - Grouping Data
Jul 30, 2007Hello,
I have a table similar to the following (XYZ). I would like to write a select statement that will return the count of the unique items for each user that also happen to be less than 1 year old. The less than one year old part is rather easy dateadd(year, -1, getdate()), but I seem to be having a hard time figuring out how to get my desired result without using subselects. Any help greatly appreciated. Thanks in advance - Dan.
So my goal results are:
User Count
Dan 2
Dave 1
Table XYZ
ID User Item Value Date
1 Dan 1 20 5/5/2007
2 Dan 1 30 6/5/2007
3 Dave 2 25 6/1/2007
4 Dan 2 22 5/1/2007
5 Dan 3 23 5/1/2006
Transact SQL :: Query To Generate XML And Grouping?
Jul 29, 2015I am trying to generate XML path from a SQL Server Table. Table Structure and sample data as below
CREATE TABLE #OfferTransaction
( [OfferLoanAmount1] INT
,[offferid1ProgramName] VARCHAR(100)
,[Offer1LenderName] VARCHAR(100)
,[offerid1LenderNMLSID] INT
[code]....
what changes do I need in my query so that the XML looks like the one above ( DESIRED XML). Is it possible via query changes?
Displaying Results In Aggregate Query Without Grouping Everything?
Mar 13, 2012I have a query where I have customers, date they ordered a swatch, date they ordered an item, and eh date diff between the two. I want to show the MIN date diff for each customer, and also show the swatch date and item date as well. But to use the MIN aggregate, it forces me to group everything, where I just want to group by customer, and have the 2 dates tag along, because i only want one record per customer. What is the easiest way for me to accomplish this?
SAMPLE:
CustKeySwatchDateRugDateDateDiff
10903963126678366
10903963126837525
10903963126960648
10913962286550322
1115886193625764
1129666456646711
1146986229625324
1146986229627647
11469862296667438
1146986656666711
1146986624666743
DESIRED RESULTS:
CustKeySwatchDateRugDateDateDiff
10903963126678366
1115886193625764
1129666456646711
1146986656666711
SQL Server 2008 :: Query Grouping By Month
Mar 5, 2015I have the query below which produces a succesful output but as there is more than one course date the month appears for example three times where there are three courses in Jan as the example output below how can I change the query to group these
MonthYear CCG AttendedCity CCG DNACity CCG Cancelled
Oct2014010
Jan2015000
Jan2015000
Jan2015100
Feb2015000
Mar2015210
May2015010
SQL QUERY
SELECT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, SUM(CASE WHEN a.AttendanceStatus IN (9)
THEN 1 ELSE 0 END) AS [City CCG Attended], SUM(CASE WHEN a.AttendanceStatus IN (3) THEN 1 ELSE 0 END) AS [City CCG DNA],
[Code] ....