Aggregate Task When Query Returns Empty.
Jan 13, 2008
To create a strike file for a text file output, I piped the output of a query throught a mulitcast. One output is the actual flat file, the other output is the strike file. The next task for the strike output is an aggregate count and sum functions. If the original query returns no records, the count shows zero, but the sum shows NULL. Further down the process I have to pad out the values with zero's, but that NULL is causing problems. How do I convert the Null to either an empty string, or a value of zero?
Thanks
View 1 Replies
ADVERTISEMENT
Feb 26, 2007
@Names is a query string passed in, I need to count the number of records as a result of the below query/
Dim test As String
Dim sqlConnection3 As New SqlConnection("data Source=EQ-520-WEBSQLEXPRESS;Initial Catalog=CRDB.MDF;Integrated Security=True")
Dim cmd As New SqlCommand
Dim returnValue As Object
cmd.CommandText = "SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager = @Names)"
cmd.CommandType = Data.CommandType.Text
cmd.Connection = sqlConnection3
cmd.Parameters.Add("@Names", Data.SqlDbType.NVarChar)
sqlConnection3.Open()
cmd.Parameters("@Names").Value = test
If test = "" Then
Response.Write("An error occured")
Exit Sub
Else
returnValue = cmd.ExecuteScalar()
sqlConnection3.Close()
Label6.Text = "Number " & returnValue
View 1 Replies
View Related
May 7, 2007
Good day,
I just like to ask if anybody has experienced getting empty rows from SQL data adapter? I'm using SQL Server 2005. Problem is when the sql is run on Query Analyzer it retrieves a number of rows but when used in my application it returns 0 or empty rows.
I thought the connection is not the problem since I got my columns right. Below is my code snippet.
Thanks!
const string COMMAND_TEXT = @"select distinct somefield as matchcode, count(somefield) "
+ "as recordcount from filteredaccount where StateCode = 0 group by somefield having count(somefield) > 1";
SqlDataAdapter adapter = new SqlDataAdapter(COMMAND_TEXT, connection);
DataTable dt = new DataTable(sometablename);
adapter.Fill(dt);
View 13 Replies
View Related
Nov 30, 2005
hi, im new to this site so i don't know if i'm posting in the correct forum. anyway, this is my code:---Dim dbMac As DBLibrary = Nothing
dbMac = New DBLibrary(General.GetMACConnectionString)dbMac.OpenConnection("SPR_STAFFMAIN_GETEMPLOYEERECORDS")dbMac.CreateParameter("@USERENTITYID", GetUserEntityID(), Data.SqlDbType.Int)drpEmpNumbers.DataSource = dbMac.GetDataView("StaffMaintenance")gridStaffMaintenance.DataSource = dbMac.GetDataView("StaffMaintenance")gridStaffMaintenance.DataBind()---DBLibrary is a class that opens a connection to the SQL server. i'm getting an empty grid even though the stored procedure returns a row when i test it in the analyzer. is there to debug or test this code? thanks!
View 1 Replies
View Related
Feb 1, 2006
Hi, whenever the underlying query being called by EXEC in the followinghas an empty result set I get the following error -- Invalid Data for'Numeric' when EXEC returns empty row. However if I call the querywithout using REPLACE (which I'm forced to do, because openquery doesnot allow variables), I get just an empty result set. Whenever theunderlying query returns a non-empty result set, the code works withouterror (regardless of wether there are nulls in the numeric column).set @switch ='5707550'set @start_date = '01-JAN-2006'set @end_date = '27-JAN-2006'set @month = 1set @year = 2006set @sql_str='SELECT * FROM(select MSC_KEY,to_char(trunc(TSTAMP), ''yyyy-Mon-dd'') as "Timestamp",ROUND( NVL(SUM(SUNRGMMSCBHCP1.XASUTIL),0) / DECODE (NVL(SUM(SUNRGMMSCBHCP1.XASNXFR),0),0,NULL,NVL(SUM( SUNRGMMSCBHCP1.XASNXFR),0)), 5)as "PER_CPU_UTIL"FROM NOR_GSM_COMPOSITE_MSC1_BHCPP SUNRGMMSCBHCP1,mscs_view vWHERE SUNRGMMSCBHCP1.gsm_msc_key = v.msc_key and v.MSC_KEY in (' +@switch + ')and SUNRGMMSCBHCP1.TSTAMP between to_date(''' + @start_date + '00:00:00'', ''DD-MON-YYYY HH24:MI:SS'') andto_date(''' + @end_date + ' 23:59:00'', ''DD-MON-YYYYHH24:MI:SS'')group by MSC_KEY, trunc(tstamp))WHERE rownum < 10000'SET @sql_str = N'select * from OPENQUERY(VISION, ''' +REPLACE(@sql_str, '''', '''''') + ''')'EXEC (@sql_str);Is there anyway to prevent this error?Thanks,Crazy
View 1 Replies
View Related
Aug 1, 2007
In sql server 2000 - our QA pointed out that his testing for empty strings returned 200 + rows but that when he clicked in the field there were obviously a space there. This issue came up because of the script I created to replace and earlier one that queried on empty strings instead of datalength and the earlier script always reported that it had updated x number of rows regardless of how many times it was run on the same database.
QA query based on the earlier script:
Select * from StringTable
WHERE (LongString = '' OR LongString IS NULL)
My script:
The fields are nvarchars in the newer database but older version of the database had varchars. I had created a script to replace empty strings as follows:
-- if LongString column is varchar - run varchar update else nvarchar update
If exists (Select * from sysobjects o
inner join syscolumns c on c.id = o.id
where c.name = 'LongString' and o.name = 'StringTable' and c.xtype = 167) begin
-- update varchar LongString
UPDATE StringTable
SET LongString = char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(LongString ) < 1) OR LongString IS NULL)
END
Else Begin
-- update nvarchar LongString
UPDATE StringTable
SET LongString = char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(LongString ) < 2) OR LongString IS NULL)
END
If exists (Select * from sysobjects o
inner join syscolumns c on c.id = o.id
where c.name = 'ShortString' and o.name = 'StringTable' and c.xtype = 167) begin
UPDATE StringTable
SET ShortString= char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(ShortString) < 1) OR ShortString IS NULL)
END
Else Begin
-- update nvarchar ShortString
UPDATE StringTable
SET ShortString= char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(ShortString) < 2) OR ShortString IS NULL)
END
My method for checking for datalength appears to work correctly why doesn't the QA script? I thought it might have to do with the nvarchar used in the table but I changed the column to a varchar and still has the same issue.
Thanks
View 5 Replies
View Related
Mar 3, 2008
Hi all I am having some issues in selecting items from my database where the record is NOT NULL. I have the code below however although some fields do contain soem data in it, others are blank which I believe are empty spaces. How do I do a SELECT command which ignores empty spaces and NULLS?
Code Snippet
SELECT CustomSearch FROM OfficesTable WHERE CustomSearch IS NOT NULL
Thanks, Onam.
View 10 Replies
View Related
Feb 28, 2007
Hi,
I have a column called QTY and I need to calculate the percentage for each row based on the total sum of the column. They are all decimals. I have an OLE DB source that is reading the table used an aggregate task using the QTY column with operation SUM and called the output SUM OF QTY. Then I have a Derived Column task that takes the QTY column / SUM OF QTY * 100 to create the percentage. My numbers are not coming out correctly and when I put a data viewer from the aggregate task, it shows the same number for both columns:
QTY SUM OF QTY
0.01833 0.01833 (this should be the sum of all the decimals in the QTY col)
What am I doing wrong?
Thanks in advance!
Isabelle
View 3 Replies
View Related
Feb 26, 2007
We have a Data Flow Task.
Inside this, we have a OLE DB Data Source which calls and executes a stored procedure.
We use a Row Count Task to count the number of rows returned by the OLE DB Data Dource Task.
Then, we use an aggregate function task to get the sum of all the rows of one particular column that is returned from the OLE DB Data Source.
The issue here is that we want to assign the sum value returned by the Aggregate Function Task to a User Variable named User::Variable. We have tried to assign this by using @User::Variable and User::Variable, but neither of those return the expected value.
If there is any other method to do the same then let us know.
We have checked the that the row count is greater than zero.
Any help would be very much appreciated.
Thanks in advance.
View 4 Replies
View Related
Dec 5, 2006
On my office PC I have a strange problem with Data Flow Tasks. When I check-in a package into Source Control (Team Foundation) I usually have some empty Data Flow Tasks in the packages...even if I haven't changed that Flow Task.
This is really frustrating cause when running the package all goes well, but after a whil you notice that some tables are empty.
Does anyone know what the problem is?
View 18 Replies
View Related
Sep 7, 2006
I have a field called CustomerName Varchar 100 and I wish to write a function that can do the following in a script component task
create a function called CleanString (ByVal CustomerName as String) As String
CleanString Returns the last word of a Customer name if the CustomerName field contains more than one word or if the CustomerName field does not contain Corp or Ltd
ie parse 'Mr John Tools' and the function returns 'Tools'
ie parse 'TechnicalBooks' and the function returns 'TechnicalBooks'
ie parse 'Microsoft Corp' return 'Microsoft Corp'
ie parse 'Digidesign Ltd' return 'Digidesign Ltd'
Any idea of a regular expression or existing piece of existing code I can have
thanks in advance
dave
View 3 Replies
View Related
Jun 2, 2011
In my current project i have a requirement to assign value of an aggregate transform to a variable. But i need to accomplish it without using a script task.
View 3 Replies
View Related
Oct 7, 2014
I am trying to create a SSIS package that will create a csv of a dataset for daily events in the database. However there will be days that there was no activity and thus an empty dataset. The package still runs fine but I want to stop the package if the dataset is empty.
FLOW:
DATA FLOW task: get daily data and put in CSV file
FTP TASK: upload the file to FTP server
MAIL/Copy file task: Move the file and then send a confirmation mail on task completion status.
Pretty simple and it all works great, I do have a few complexities in there. What I would like to add and I am at a loss is at the beginning, if the OLE DB Task resultset is empty then move to Mail Task otherwise process normally. I have tried conditional split, derived columns, the only thing I haven't tried in Script task and am not sure about that yet.
View 4 Replies
View Related
Jun 17, 2015
I am attempting to get this script provided by Microsoft to work to no avail. Specifically, when I set the variable FFNonDataRows to 1 (in order to accommodate for the header row), the variable is not being set to False as expected. I don't know enough about C# to understand why this script isn't working. How to get this script to work in this manner?
[URL] ....
View 6 Replies
View Related
Jun 17, 2007
Hi,
I have we have a client who gives their invoices in a flat file format, we import it into a SQL Server table.
Nothing is normalized – everything is repeated in every record. The fields are:
customerNumberInvoice_numberPO_numberQtyDescriptionLine_numberLine_totalFreightTaxInvoice_date
So an if an order has 10 line items, the header information (invoice number, PO number, ivoice date) are repeated on each of the lines
I am writing a query to show the following
Order number, Invoice total, Date
select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice_date from invoices group by invoice_no, Invoice, customerNumber
This works great - for each invoice I get the invoice number, InvoiceTotal, and Date
Then I was asked to add the PO Number – this is where I can’t get it right.
When I added “PO_number� to the query, I got two lines for each invoice
select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice, PO_number from invoices group by invoice_no, Invoice, Sold_To_Cust_No, PO_number
Please help - I need to end up with: invoice_no, invoiceTotal, customerNumber, Invoice_date and PO_number (sequence does not matter)
Thanks
View 4 Replies
View Related
Dec 21, 2007
I am attempting to wrie a query that will return aggregate totals from two different tables. The problem is that the TotalForecast totals are way to high. How do I write a query to obtain the correct totals?Table 1 - dbo.QM_ResultsColumns - dbo.QM_Results.Special8, dbo.QM_Results.SessionName, dbo.QM_Results.PersonNumberTable 2 - dbo.PM_ForecastViewColumns - dbo.PM_ForecastView.Hierarchy, dbo.PM_ForecastView.ForecastSelect substring(dbo.QM_Results.Special8,0,6) AS Hierarchy, substring(dbo.QM_Results.SessionName,0,11) As CourseCode,count(dbo.QM_Results.PersonNumber) TotalAssociates,sum(dbo.PM_ForecastView.Forecast) TotalForecastFrom dbo.QM_Results INNER JOIN dbo.PM_ForecastView ON dbo.PM_ForecastView.Hierarchy = substring(dbo.QM_Results.Special8,0,6)where SessionMid in ('96882139', '23620891', '45077427', '29721437')AND substring(dbo.QM_Results.Special8,0,6) in ('EZHBA')Group By substring(dbo.QM_Results.Special8,0,6),substring(dbo.QM_Results.SessionName,0,11)Sample of data returned with my current query.Hierarchy CourseCode TotalAssociates TotalForecastEZHBA CARD167200 1179 141480EZHBA CARD167201 1416 169920EZHBA CARD167202 1119 134280EZHBA CARD167204 99 11880Results when I run aggregate query separatelyActual Total takenHierarchy CourseCode TotalTakenEZHBA CARD167200 393EZHBA CARD167201 472EZHBA CARD167202 373EZHBA CARD167204 33Forecasted Total takenHierarchy CourseCode ForecastEZHBA CARD167200 999EZHBA CARD167201 900EZHBA CARD167202 800EZHBA CARD167204 800
View 4 Replies
View Related
Jul 11, 2005
I have two tables tb1 with item and qtyOnHand and a second table tb2 with item and qtyOrdered I am trying without success to make this happen;select sum (onHand-Ordered) from (select sum (qtyOnHand) from tb1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from tb2 where item = RD35 group by item) as OrderedI kind of gathered it would work based on this http://weblogs.asp.net/jgalloway/archive/2004/05/19/135358.aspxI have also tried this;select tb1.item from (select sum (qtyOnHand) from tb1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from tb2 where item = RD35 group by item) as Ordered, sum (onHand-Ordered) as available from tb1 where tb1.item = RD35Any ides, there are multiple rows of each item in each table tb1 is inventory with several different locations and tb2 is an orders table.
View 3 Replies
View Related
Feb 13, 2014
I would like to find the first transaction_date with the criteria below and return all transactions after. i would need to use the seqn number since many of the transactions could occur on the same day.
SELECT c.MEMBER_TYPE
, c.DATE_ADDED
, h.ID
, h.ACTIVITY_TYPE
, h.TRANSACTION_DATE
, h.UF_1
, min (h.seqn)
FROM
[code]....
View 3 Replies
View Related
Feb 29, 2008
I have two tables ItemHistory and ItemStock.
I would like to write a query which checks last years history and let us know if we have enough items in stock this year for a given span date.
First, It should get all items from @ItemHistory where WHERE DateSold >= '1/10/2007' AND DateSold < '1/11/2007'
and then checks if corresponding items are found in @ItemStock,
and then returns all the ItemID where sum(@ItemStock.Quantity) < sum(@ItemHistory.Quantity)
Thank You.
Here is the DDL and DML
DECLARE @ItemHistory TABLE (
ItemID INT,
Quantity INT,
DateSold DATETIME )
INSERT INTO @ItemHistory
SELECT 12, 18, '2007-01-10' UNION ALL
SELECT 12, 18, '2007-01-10' UNION ALL
SELECT 26, 12, '2007-01-10' UNION ALL
SELECT 28, 06, '2007-01-10' UNION ALL
SELECT 29, 06, '2007-01-10' UNION ALL
SELECT 30, 06, '2007-01-10' UNION ALL
SELECT 31, 06, '2007-01-10' UNION ALL
SELECT 31, 06, '2007-01-10' UNION ALL
SELECT 32, 12, '2007-01-10' UNION ALL
SELECT 33, 01, '2007-01-10' UNION ALL
SELECT 33, 06, '2007-01-10' UNION ALL
SELECT 36, 01, '2007-01-10' UNION ALL
SELECT 52, 12, '2007-01-10' UNION ALL
SELECT 83, 01, '2007-01-10' UNION ALL
SELECT 36, 12, '2007-01-10' UNION ALL
SELECT 37, 01, '2007-01-10' UNION ALL
SELECT 38, 12, '2007-01-10' UNION ALL
SELECT 17, 01, '2007-01-10' UNION ALL
SELECT 17, 08, '2007-01-10' UNION ALL
SELECT 12, 20, '2007-02-20' UNION ALL
SELECT 26, 10, '2007-02-20' UNION ALL
SELECT 30, 08, '2007-02-20' UNION ALL
SELECT 31, 12, '2007-02-20'
DECLARE @ItemStock TABLE (
ItemID INT,
Quantity INT )
INSERT INTO @ItemStock
SELECT 12, 20 UNION ALL
SELECT 12, 10 UNION ALL
SELECT 14, 48 UNION ALL
SELECT 17, 24 UNION ALL
SELECT 19, 36 UNION ALL
SELECT 19, 72 UNION ALL
SELECT 20, 72 UNION ALL
SELECT 26, 24 UNION ALL
SELECT 28, 12 UNION ALL
SELECT 29, 12 UNION ALL
SELECT 30, 12 UNION ALL
SELECT 31, 18 UNION ALL
SELECT 32, 20 UNION ALL
SELECT 32, 68 UNION ALL
SELECT 33, 10 UNION ALL
SELECT 35, 18 UNION ALL
SELECT 36, 46 UNION ALL
SELECT 36, 40 UNION ALL
SELECT 37, 30 UNION ALL
SELECT 38, 10 UNION ALL
SELECT 38, 33
View 3 Replies
View Related
Jan 4, 2008
Hi all. Here's my question:
I have a database table that contains rows containing defect data. The two columns I'm interested in are device and severity. Device is the name of an item that contains the defect, and severity can be one of three values. I'd like to query the table so it shows the device, and the number of instances of each of the three values for that device. For example, the result table should look like this:
device Severity 1 Severity 2 Severity 3
Widget 12 15 24
In this example, There are 51 rows containing Widget as the device. 12 have severity 1, 15 have severity 2, and 24 have severity 3.
Seems like it should be a pretty simple query, but I'm kinda new to SQL and I'm having trouble. Help would be much appreciated.
Thanks.
View 5 Replies
View Related
Feb 23, 1999
When I was using a simple query using select statement with where clauses, I can get the results. When
I use AND to specify more conditions. It returns no row even though I get the result when query seperately.
What should be the possible cause of this. I am using SQL Server 6.5. Thank you
View 2 Replies
View Related
Nov 30, 2006
when I run a package from a command window using dtexec, the job immediately says success.
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 3:37:41 PM
Finished: 3:37:43 PM
Elapsed: 2.719 seconds
However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.
Thanks,
Larry
View 1 Replies
View Related
Aug 8, 2007
I have a table similar to the following:
ID ¦ Name ID ¦ Period From ¦ Period To ¦ Percentage ¦
---------------------------------------------------------------------------
Important - Each person can have more than one entry.
What I am trying to do is get the last percentage that each person obtained.
The only way I have been able to do this is by the following:
SELECT * FROM myTable
LEFT OUTER JOIN ( SELECT NameID, MAX(PeriodTo) as PeriodTo FROM myTable GROUP BY NameID) t1
ON myTable.NameID = t1.NameID
WHERE myTable.PeriodTo = t1.PeriodTo
I was wondering if there was another way of doing this, or whether this is an efficient method of doing this kind of query. Jagdip
View 8 Replies
View Related
May 26, 2004
Hi all,
I need to build a query that aggregates the orders that have the same items,ex:.
ORDERS:
ID NUMBER
------------ ----------
1 123
2 456
3 789
ITEMS:
ORDERFK NAME
------------ --------
1 A
1 B
2 A
2 C
3 A
3 B
the result should be:
TOT_ORDERS:
NEW_ID QUANT
------------ ---------
1 2
2 1
NEW_ITEMS:
TOT_ORDERFK NAME
----------------- --------
1 A
1 B
2 A
2 C
Some ideia?
ThX
NeuralC
View 6 Replies
View Related
Jun 22, 2005
I want to return a list that contains each employee's ID, the date of their last payrate adjustment, and their current payrate. Note that in the table below, employee 1002 was a bad boy in March of 2005, and his hourly rate was reduced to 14.00.
TableName: Employees
EmployeeID............Date.............PayRate
-------------------------------------------
....1001...............1/24/03............12.50
....1002...............2/28/03............12.75
....1003...............5/14/03............10.50
....1002...............3/15/04............15.00
....1001...............6/22/04............14.00
....1002...............3/16/05............14.00
The result set should look like:
EmployeeID...........Date.............PayRate
-----------------------------------------
...1003................5/14/03............10.50
...1001................6/22/04............14.00
...1002................3/16/05............14.00
View 1 Replies
View Related
Jan 15, 2014
I am trying to use the following syntax and it is saying I can't use an aggregate function in a subquery. I can't use a GROUP BY in this case because if another field in the project table (such as status) is different, that project will show up twice.So in this case I am using this syntax to show the most recent quote within the project.
SELECT PROJECT.*, QUOTE.QuoteDate, QUOTE.QuoteCode
FROM PROJECT LEFT JOIN QUOTE ON PROJECT.ProjectID = QUOTE.ProjectID
WHERE QUOTE.QuoteDate=(SELECT Max(Q.QuoteDate) FROM QUOTE Q WHERE Q.ProjectID = PROJECT.ProjectID);
My goal here is to show the most recent quote within each project (there can be multiple revisions of a quote within each project). I want to show other fields such as the status of the quote, but if the status is different between quotes, the GROUP BY on that field will cause it to be listed more than once. All I want to show is the most recent quote for each project.
View 3 Replies
View Related
Jul 23, 2005
I'm working on a fairly complex query, and to avoid exploding my brainany further, I'm going to keep this simple with an example of what I'mtrying to do...Let's say you had a table of customers and a table of ordersAnd you had data that looked like this:CustomersCustID CustName-------------------------------1 Billy2 JohnOrdersCustID Amount PaidYN-------------------------------1 $2 12 $3 01 $4 11 $2 02 $5 0And you wanted a report that looked like this:Customer TotalPaid TotalUnpaid------------------------------------------Billy $6 $2John $0 $8How would you write a query to generate this report?
View 2 Replies
View Related
Dec 10, 2012
I have written sql query
select INVOICE.InvoiceTypeCode, INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,
INVOICETYPEMASTER.InvoiceTypeName, INVOICEITEMS.ItemQuantity as SumQuantity,
INVOICE.BasicValue ,INVOICE.BasicValue * INVOICE.ExchangeRate +
[Code].....
I am getting different amount 984000.0000 and quantity 9.
View 1 Replies
View Related
Oct 19, 2007
Can any1 tell me why i am getting an error
SELECT DISTINCT
--p.voucher,
--p.amount,
p.siteID,
b.siteID,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS OutStandingBalance,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS CashCheque,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS Vouchers
FROM
BillingTotal b,
Payment p
--WHERE
-- s.sitename=@cmb1
--AND p.siteid = s.siteid
-- p.voucher = 0
-- p.voucher = 1
GROUP BY p.siteID,b.siteID
Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
View 8 Replies
View Related
Sep 22, 2015
I have 2 Dimensions in SSAS (see below end), I want to create a calculated member, named
This_Year_Billable_Objective_Count, with its formula = BillableLastYear*(100+ BillableObjective)/100.
The first factor, Â BillableLastYear is a number, aggregated (sum) from child units.
The second factor, Â BillableObjective is a percent number (for example 28 means 28%), it's not aggregate. It's an dependent value for each unit.
How can I calculate This_Year_Billable_Objective_Count for each unit?
\ able 1
SELECT [UnitKey]
   ,[UnitID]
   ,[UnitName]
   ,[Unit2Name]
   ,[Unit3Name]
   ,[Unit4Name]
[Code] .....
View 6 Replies
View Related
Oct 17, 2006
I currently have the following query:
Quote:
select distinct a.memberFirstName, a.memberLastName, c.ChapterName, d.divisionName,
count(f.memberID) as numMembers
FROM Members a
INNER JOIN groupLeaders b
ON a.memberID = b.memberID
Inner JOIN Chapters c
ON c.chapterID = b.chapterID
LEFT JOIN divisions d
ON d.divisionID = c.divisionID
Inner Join groupsOfEight e
ON e.groupLeaderID = b.groupLeaderID
Inner Join groupOfEightMembers f
ON f.groupOfEightID = e.groupOfEightID
Group BY a.memberFirstName, a.memberLastName, c.chapterName, d.divisionName
Order By divisionName, numMembers
This query returns me the names of all of my Group Leaders, their Chapter, Division, and the number of members they have selected to be in their group.
Now, instead of the number of members in each Group I would like to know the total number of Members in each division to appear in the count.
[NOTE: All chapters have a division, linked by a divisionID in the "Chapters" table -- I need to get a count of all the "ChapterMembers" [chaptermembers is a table also] that are in the Division.
Here is the query I started to build before I ran into serious trouble:
Quote:
select a.divisionName, count('c.memberID') as numMembers
From Divisions a
Inner Join Chapters b
On b.divisionID = a.divisionID
Inner Join chapterMembers c
ON c.chapterID = b.chapterID
Left Join Members d
ON d.memberID = c.memberID
LEFT Join groupLeaders e
On e.memberID = d.memberID
Group By a.divisionName
This particular query returns only the DivisonName and the number of Members in the division as expected. However, when I try to select the information for the GroupLeader (first & last name) I am forced to add memberFirstName to the Group By statement which changes my Count...
Have I done an okay job of explaining the problem?
The goal here is to select all of the GroupLeaders first & last name, their chapterName, divisionName, and the total number of members in the division.
Thanks for any advice!
Zoop
View 3 Replies
View Related
Jun 1, 2008
I have a table as below:
COMPUTERNAME, COUNTER, REASON
WXP-13Failed
WXP-1113Failed
WXP-38Failed
WXP-910Failed
WXP-117Success
What I would like to do is create a percentage of Success vs. the Failed for the same Computername.
Problem is when I use Group by I cannot tell the COUNTER of the Success anymore. This is what I have been using:
SELECT COMPUTERNAME, SUM(COUNTER) AS COUNTERTOTAL
FROM
ReturnTable
GROUP BY COMPUTERNAME
Thank you. The above query actually targets a result table from another query, but that should not matter.
View 11 Replies
View Related
Nov 29, 2012
I am having a query
select INVOICE.TarrifHeadNumber, SUM(INVOICEITEMS.ItemQuantity) From invoiceitems,
invoice Where invoice.invoicenumber = invoiceitems.invoicenumber and
month(InvoiceDate)='11' and year(InvoiceDate)= '2012'
group by INVOICE.TarrifHeadNumber
tarrifheadno SUM(INVOICEITEMS.ItemQuantity)
84195030 9.00
84198910 5.00
84212190 223.00
84569090 247.00
84799040 1138.00
8481-80-1030 137.00
85433000 6177.20
tarrifheadno is unique
Now if i use below query and add invoicetypecode field
select INVOICE.TarrifHeadNumber,CETSH.GoodsDescription, SUM(INVOICEITEMS.ItemQuantity),INVOICE.invoicetype code From invoiceitems,
invoice , cetsh Where invoice.invoicenumber = invoiceitems.invoicenumber and
month(InvoiceDate)='11' and year(InvoiceDate)= '2012' and
cast(CETSH.CETSHNumber as varchar) = INVOICE.TarrifHeadNumber group by INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,in voicetypecode
This query return distinct of 11 records
84195030 84195030
9.00 1
84198910 84198910
5.00 2
84212190 84212190
157.00 1
84212190 84212190
42.00 2
84212190 84212190
24.00 3
84569090 84569090
189.00 1
84569090 84569090
58.00 2
84799040 84799040
166.00 1
84799040 84799040
972.00 2
85433000 85433000
3764.00 1
85433000 85433000
2413.20 2
How to get same 7 records
View 1 Replies
View Related
