OLAP Design Or MDX Syntax To Handle 1-many And Distinct Counts
Jan 30, 2008
All,
My first post so go easy. Also, still a bit green on both OLAP and MDX but I need help figuring this out.
The data set I'm using has patient level information and I'm interested in distinct counts of patients. There are several dimensions I have in my existing cube but there are three different types. I have a time dimension, a 1-1 dimension and a 1-many dimension (sorry not getting the proper terminology correct here).
The time dimension (month is the lowest level) I have a handle on as well as the 1-1 dimension (lets say age or gender is a good example). For the purpose of the explanation, lets just add a cost measure for a placeholder. The bugger is the 1-many dimension. When you visit a doctor, you typically get diagnosed with a code. The code rolls up to a disease category. THis happens every visit and every visit can have multiple codes. For example, you come in with a broken arm and they diagnose you with the flu too.
The business question I'm trying to answer would be something like:
How many female patients have been diagnosed with disease Y OR disease Z or disease T in the past 12 months? and how much did it cost?
Please note in the above question, the operand is "OR" meaning they would have to present with any 1 of the three to meet the criteria. Its my understanding cubes typically will be additive in nature and try to come back with counts of females with instances of all three. This is not the case and fails the business need.
I know this is a high level question but any ideas into either a design or a raw MDX syntax to tackle the issue would help.
Thanks! jmac
View 2 Replies
ADVERTISEMENT
May 18, 2007
On my company site, I have created a database that is for the purpose of tracking google adwords, as well as pages that the user visits. For instance, if you were to search for "guitars" and then click our ad, an entry is created in the database like thisKeyword: SessionGUID: PageVisited: VisitedDateTime:Guitars lkjfeilfjskdlfjsije ~ViewCategory.aspx?Cat=Guitars,KW=Guitars 12/01/2000 12:00amGuitars lkjfeilfjskdlfjsije ~ViewProduct.aspx?ProductID=1253&SubProductID=3 12/01/2000 12:03amGuitars lkjfeilfjskdlfjsije ~Search.aspx?Q=BC%20%Rich 12/01/2000 12:05am Pretty much, in our administrative area, I want to be able to have a table that would generate these results: Keyword Total HitsGuitars 3So im guessing that obviously I would need to do a select distinct for the Keyword column, but how do I also have a column showing a count of the records?
View 1 Replies
View Related
Jan 15, 2015
I am trying to create a query that outputs the following data from multiple tables. Here is an example of the raw data right now
Date | MachineNumber | TestName
---------------------------------------
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 500 | Something
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 510 | NewTest
1/1/2015 | 620 | Test#1
Here is the desired counted output, I would like to pull distinct Date, MachineNumber, TestName and then count how many times they occur in the raw data form.I do need to perform a case on the date because right now its in a datetime format and I only need the date.
Date | MachineNumber | TestName | TestOccuranceCount
-----------------------------------------------------------------
1/1/2015 | 500 | Something | 4
1/1/2015 | 510 | NewTest | 3
1/1/2015 | 620 | Test#555 | 1
I am pulling three columns with the same names from 8 different tables. What I need to display the date, machine & test name and count how many times a test was run on a machine for that date. I have a feeling this can be handled by SSAS but haven't built an analysis cube yet because I am unfamiliar with how they work. I was wondering if this is possible in a simple query. I tried to set something up in a #Temp table. Problem is the query takes forever to run because I am dealing with 1.7 Million rows. Doing an insert into #temp select columnA, columnB, columnC from 8 different tables takes a bit.
View 9 Replies
View Related
Sep 28, 2007
I am using SSRS 2005 to develop this OLAP report. I have the "Distinct Count" as a measure and some dimensions in the OLAP cube. I assigned one of the dimension to the "row" grouping and use the wizard to add the "subtotal" to that row level dimension. However it adds up individually the "Distinct Count" values from that row level dimension instead of the "True Distinct Count" of all attributes of that dimension combined. In my report there are "column" groupings so that by creating another dataset just to calculate the distinct count seperately in the report will not work unless there is a way to link this dataset to the column grouping of the first dataset. Also I don't want to create this dataset as I need to pull in the individual "id" field in the dimension in order to do the "Distinct Count" in the report and it is slowing down the performance. Thanks.
View 1 Replies
View Related
Jan 28, 2008
Hi guys,
Could you please help me. I have an an error message of this upon running this: "Incorrect syntax near the keyword 'distinct'.
'OldIdNo' = distinct (C.Id1 + C.Id2 + C.Id3 + C.Id4)
View 7 Replies
View Related
Jul 20, 2005
I'm trying to order a varchar column first numerically, and secondalphanumerically using the following SQL:SELECT distinct doc_numberFROM doc_lineWHERE product_id = 'WD' AND doc_type = 'O'ORDER BY CASE WHEN IsNumeric(doc_number) = 1THEN CONVERT(FLOAT, doc_number)ELSE 999999999END,CASE WHEN IsNumeric(doc_number) = 1THEN 'ZZZZZZZZZ'ELSE doc_numberEND;When try executing this statement, I get the following error:Server: Msg 145, Level 15, State 1, Line 1ORDER BY items must appear in the select list if SELECT DISTINCT isspecified.If I take the "distinct" out, it works just fine, except for the fact that Iget many duplicates.Does anyone have any suggestions?Thanks,Frank
View 3 Replies
View Related
Sep 21, 2015
I have the below query,
select
a.Assignment_UniqueID as DeploymentID,
a.AssignmentName as DeploymentName,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
sn.StateName as LastEnforcementState,-----Required Column
[Code] ....
o/p: is :
LastEnforcementState NumberOfComputers PComputers
Compliant 7056 91.54
Downloading update(s) 39 0.51
[code]....
I want to add these two rows information into single row,
Failed to Download Updates(s) 131(127+8) 1.66(1.65+0.1)
how to do this in my sql query?
View 12 Replies
View Related
Sep 19, 2000
Hello All,
Our OLAP environment involves an ETL/Data Warehouse/Data Mart server and a cube publisher server.
We would like to learn how to automate the Archival/Restore of OLAP databases. We are currently doing
it manually though OLAP Manager. Any help would be appreciated. Thanks. James.
--
James E. Bothamley
Senior Database Administrator
Dave & Buster's, Inc.
2481 Manana
Dallas, TX 75220
Work
Phone (214) 904-2296
email jbothaml@DaveAndBusters.Com
"Once in a while you can get shown the light
in the strangest of places if you look at it right"
JG 1942-1995 RIP
View 1 Replies
View Related
Jun 12, 2007
How to repair a corrupted OLAP database?
View 2 Replies
View Related
Dec 3, 2007
Hi
I'm using service broker and keep getting errors in the log even though everythig is working as expected
SQL Server 2005
Two databases
Two end points - 1 in each database
Two stored procedures:
SP1 is activated when a message enters the sending queue. it insert a new row in a table
SP2 is activated when a response is sent from the receiving queue. it cleans up the sending queue.
I have a table with an update trigger
In that trigger, if the updted row meets a certain condition a dialogue is created and a message is sent to the sending queue.
I know that SP1 and SP2 are behaving properly because i get the expected result.
Sp1 is inserteding the expected data in the table
SP2 is cleaning up the sending queue.
In the Sql Server log however i'm getting errors on both of the stored procs.
error #1
The activated proc <SP 1 Name> running on queue Applications.dbo.ffreceiverQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
error #2
The activated proc <SP 2 Name> running on queue ADAPT_APP.dbo.ffsenderQueue output the following: 'The conversation handle is missing. Specify a conversation handle.'
I would appreceiate anybody's help into why i'm getting this. have i set up the stored procs in correctly?
i can provide code of the stored procs if that helps.
thanks.
View 10 Replies
View Related
Jul 6, 2007
Hi, I have the following script segment which is failing:
CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))
INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache
When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."
Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.
The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.
Any ideas?
View 2 Replies
View Related
Jan 18, 2008
We have implemented our service broker architecture using conversation handle reuse per MS/Remus's recommendations. We have all of the sudden started receiving the conversation handle not found errors in the sql log every hour or so (which makes perfect sense considering the dialog timer is set for 1 hour). My question is...is this expected behavior when you have employed conversation recycling? Should you expect to see these messages pop up every hour, but the logic in the queuing proc says to retry after deleting from your conversation handle table so the messages is enqueued as expected?
Second question...i think i know why we were not receiving these errors before and wanted to confirm this theory as well. In the queuing proc I was not initializing the variable @Counter to 0 so when it came down to the retry logic it could not add 1 to null so was never entering that part of the code...I am guessing with this set up it would actually output the error to the application calling the queueing proc and NOT into the SQL error logs...is this a correct assumption?
I have attached an example of one of the queuing procs below:
Code Block
DECLARE @conversationHandle UNIQUEIDENTIFIER,
@err int,
@counter int,
@DialogTimeOut int,
@Message nvarchar(max),
@SendType int,
@ConversationID uniqueidentifier
select @Counter = 0 -- THIS PART VERY IMPORTANT LOL :)
select @DialogTimeOut = Value
from dbo.tConfiguration with (nolock)
where keyvalue = 'ConversationEndpoints' and subvalue = 'DeleteAfterSec'
WHILE (1=1)
BEGIN
-- Lookup the current SPIDs handle
SELECT @conversationHandle = [handle] FROM tConversationSPID with (nolock)
WHERE spid = @@SPID and messagetype = 'TestQueueMsg';
IF @conversationHandle IS NULL
BEGIN
BEGIN DIALOG CONVERSATION @conversationHandle
FROM SERVICE [InitiatorQueue_SER]
TO SERVICE 'ReceiveTestQueue_SER'
ON CONTRACT [TestQueueMsg_CON]
WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER ( @conversationHandle )
TIMEOUT = @DialogTimeOut
-- insert the conversation in the association table
INSERT INTO tConversationSPID
([spid], MessageType,[handle])
VALUES
(@@SPID, 'TestQueueMsg', @conversationHandle);
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
ELSE IF @conversationHandle IS NOT NULL
BEGIN
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [TestQueueMsg] (@Message)
END
SELECT @err = @@ERROR;
-- if succeeded, exit the loop now
IF (@err = 0)
BREAK;
SELECT @counter = @counter + 1;
IF @counter > 10
BEGIN
-- Refer to http://msdn2.microsoft.com/en-us/library/ms164086.aspx for severity levels
EXEC spLogMessageQueue 20002, 8, 'Failed to SEND on a conversation for more than 10 times. Error %i.'
BREAK;
END
-- We tried on the said conversation, but failed
-- remove the record from the association table, then
-- let the loop try again
DELETE FROM tConversationSPID
WHERE [spid] = @@SPID;
SELECT @conversationHandle = NULL;
END;
View 2 Replies
View Related
Mar 12, 2007
I need to run a SELECT DISTINCT query acrossmultiple fields, but I need to add another field that is NON-DISTINCTto my record set.Here is my query:SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, genderFROM gpresultsWHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis query runs perfect. No problems whatsoever. However, I need toalso include another field called "admitdate" that should be treatedas NON-DISTINCT. How do I add this in to the query?I've tried this but doesn't work:SELECT admitdateFROM (SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, gender from gpresults)WHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis has to be simple but I do not know the syntax to accomplishthis.Thanks
View 2 Replies
View Related
May 8, 2006
Hello,
I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a tableCREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime )
This app also calculates the total number of proposals coming from a specific branch in a given date bySELECTing COUNT(BranchID) FROM Proposals WHERE BranchID=@prmBranchID AND Proposal_Date=@prmDateand prints them in a table (my target table).
This target table has as many rows as the result of the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the"SELECT DISTINCT BranchID FROM Proposals". This target table converts the DateTime values ToShortDateString so that we are able to see comfortably which branch office has sent how many proposals in a given day.
So far so good, and everything works fine except one thing:
Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and11.11.2005 10:45:30) cause some trouble in the target table, where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows)
What I need to do is to get a result like (in a neat fashion :)
"SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals"
So, how to do it in a suitable way?
Thanks in advance.
View 4 Replies
View Related
Feb 5, 2007
If I right click and browse the properties for the table I can get the value of rows. But for the same table if I do select count(*) from table the value does not match the table properties rows. Please can some one tell me why this is so?
SQL Newbie
View 2 Replies
View Related
Jan 8, 2007
I expect to get a record below with a count of 0 (and I do), but when I take the comments out (--) of lines 1 & 6 I don't understand why I get no records at all. I need to be able to see all teams in EvalAnswers even if none of the records satisfies the where clause.1 select Count(*) as cnt--, TeamID
2 from EvalAnswers
3 where CoID=@CoID
4 and EvaluatorID=@EvaluatorID
5 and (Scr0=0 and Sugg0 is NULL)
6 --group by TeamID
7
View 4 Replies
View Related
Sep 6, 2007
I need to create a view that shows the number of times that clients made payments, how many clients, and how much they paid over a period of time. I'm not sure if I can use a case for this. How can I put something like this together?
I have a tblClients with a clientid field
I have a tblPayments with the clientid, pmtdate, and pmtamount
For example:
1 Payment ----- 23 Clients ----- $16000
2 Payments ----- 12 Clients ----- $32000
3 Payments ----- 4 Clients ----- $13000
etc...
View 3 Replies
View Related
Nov 15, 2000
I need a procedure that will go to a database and give me all the row counts for the user tables.
Does anyone know how I can get this?
Thanks,
Dianne
View 3 Replies
View Related
Apr 20, 2004
Hi,
...giving a very 'summarized' scenario of the problem I have trying to
solve all day (make it 2 days now).
Below are the relevant DDLs... I am not listing the DDLs of my other tables:
CREATE TABLE [SalesFACT] (
[varchar] (10),
[TransDate] [varchar] (10),
[SaleAmt] [float],
[CustCode] [varchar] (10)
. . .
)
I populate the above table via a DTS and have checked and have verified that correct data is coming in... I also have a product master table; for business reasons we can have the same product created with different ProductCodes though the rest of the Product details are EXACTLY the same. We have covered this using a field named 'UniqueProdCode'.
CREATE TABLE ProdMaster(
[ProdCode] [varchar] (10),
[ProdName] [varchar] (35),
[UniqueProdCode] [varchar] (10),
... many other product fields e.g. unit price, category etc...
...
)
First a small Request:
Please note that I have NOT defined links between my tables (in the diagram editor) nor have I defined Primary keys (or any constraint) for any of the tables. When you kindly reply, please suggest I should define primary keys for the tables and also link them in the diagram editor.
[u]THE PROBLEM:
When I do a count(*) query on the table 'SalesFACT', I get the correct number of records.
If I create a view, add table 'SalesFACT' and table ProdMaster, link the
UniqueProdCode field of table 'SalesFACT' with the UniqueProdCode field of ProdMaster (so that I can also get the name, category, etc. for the products in the SalesFACT), and run a count(*) query I get a much higher and incorrect number of rows. The SQL for the view is:
SELECT dbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode,
dbo.SalesFACT.SaleAmt
FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.UniqueProdCode
Kindly note that I have checked and the contents of the table SalesFACT' UniqueProdCode field DOES contain the correct data i.e. it contains the UniqueProdCode and NOT the ProdCode.
But if i link the "wrong fields", I get the correct count count :confused: i.e. I create a very similar view (as mentioned above) but instead link the UniqueProdCode of table SalesFACT with the ProdCode field (not the UniqueProdCode field) of ProdMaster
table I get the correct count. This is really driving me nuts and I just can't understand what's going on and why the "REVERSE" logic. For your convenience here is the SQL for the 2nd view:
SELECTdbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode,
dbo.SalesFACT.SaleAmt
FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.ProdCode
Please guide... I have run out of all the things that I could check and thus this SOS and F1
Billions of thansk in advance.
View 2 Replies
View Related
Dec 12, 2006
I just inherited an app, where I have two tables that look like this:
[Owners]
--------
Owner
...
[Cases]
-------
Owner
Status
Assigned
...
I need a query to get results that look like this:
[Results]
--------
Owner
# of cases records where Status='Open'
# of cases records Where Status='Pending'
# of cases records WHERE Status<>'Closed' AND Assigned=''
I have one query that works already, but it's using several nested selects. I know I ought to be able to do this using group by instead, and I like to know how.
View 4 Replies
View Related
Jan 24, 2006
I am creating a database for a soccer league.
I would like to write a query that would give me results in a league table form .
How could I combine different count queries such as:
select hometeam, count(*) as homegames from matches where comp="en1pp" group by hometeam
order by hometeam
select hometeam, count(*) as homewins from matches where homescore>awayscore group by hometeam order by hometeam
into one query giving a three column result homteams, homegames, homewins.
Thanks
View 6 Replies
View Related
Sep 19, 2006
Hello :)
I seem to have somehow got myself into a situation where I'm having to run the following SELECTs, one after another, on a single ASP page. This is not tidy. How can I join them all together so I get a single recordset returned with all my stats in different columns?
SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years'
SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL
SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy'
SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULL
View 8 Replies
View Related
Oct 6, 2006
Hi all,
I have a need to compare the number of rows returned from table A when it is joined to table B to the number of rows returned when there are no joins involved. If the number of rows returned are the same, then I need to proceed to execute my next step else end.
So, If RowCount A = RowCount A when A joined to B
THEN Goto Next Step
Else End
I need to put the above logic in a sp that I want to execute using a job.
Help is appreciated.
V
View 5 Replies
View Related
Apr 22, 2004
OK I have a query that bring back a unique identifier and text value
for instance
Select Distinct global_id, Page_url from PageList
global_id page_url
---------- ---------------------------------
4306549 /true/attitudes.htm
1460753 /true/current/answerthis.htm
4303667 /true/current/answerthis.htm
4306549 /true/current/answerthis.htm
Now I want to do a count of the page_url.
Can this be done in one SQL statement using a sub query of some kind
View 6 Replies
View Related
Nov 28, 2007
Hi I have this
select emailid,
count(emailid) as 'No.of occurences',
FROM tableA
where start_moment between '2007-11-01' and '2007-11-02'
GROUP BY emailid
having (COUNT(emailid) > 1)
Fair enough this returns the emailid along with the amount of times it appears (all greater then 1 .. duplicated in other words)
My question is , there is also a start_moment field in tableA
so i need to get the max or min start_moment along with the above result?
View 3 Replies
View Related
Dec 4, 2007
I have members in a database who have paid thru dates. I am creating retention reports
I created a cross tab in Crystal (using SQL) that counts records that paid within a certain year. I need to create a script that will let me find when members skip payment for a year. Any ideas?
I was thinking of running a count of all paid (Activity) records, but still kind of stuck.
DZ
View 9 Replies
View Related
Jul 20, 2005
I want to get a resultset of every table in the database, with thecurrent record count of each. What is the easiest way to do this?I can get the list of tables with:Select s.name from sysobjects s where xtype = 'U'each s.name is a table name, but I'm not sure how to join a record countcolumn to the resultset.Thanks,RickN
View 4 Replies
View Related
Sep 19, 2007
I'm sorry if someone has already posted this but I've looked through a few pages to see if someone had already posted and I couldn't find anything. Anyways, I have two counts and I would like to divide them to get a percentage. Basically I would like to see a percentage of how many tickets are overdue. Here's my SQL:
select count(*)[No. of Tickets Overdue],
case
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
end [Ticket Status]
from whd.priority_type pt
inner join whd.job_ticket j on pt.priority_type_id = j.priority_type_id
where j.status_type_id = '1' and j.deleted = '0' and not j.priority_type_id = '5' and not j.priority_type_id = '6'
group by pt.due_hours
order by pt.due_hours desc
COMPUTE SUM(count(*))
select count(*)[Count2]
from whd.job_ticket jt
where jt.status_type_id = '1' and jt.deleted = '0'-- and not jt.priority_type_id = '5' and not jt.priority_type_id = '6'
--COMPUTE [No. of Tickets Overdue]/[Count2]
I know this isn't correct but basically the commented line at the bottom is what I want to do. I've only been doing SQL statements for a few months now, so I know its novice but any help is appreciated.
Thanks in advance.
View 7 Replies
View Related
Oct 8, 2006
Hi,
I have 10 databases created. each data base has arround 100 tables . i need to keep track of the number of records in each table of 10 databases and the last modification date on that table. The solution should be programatically by running a T-sql program or any stored procedures or any other but mechinical.
the output should specify the following:
Table #number_of_recs Last_date
database_name.Table_name ###,###,### DD/MM/YY
I will appreciate any assistance in this regard.
thanks,
View 1 Replies
View Related
May 20, 2008
I have a table that is linked to other tables in one to many relationship.I have a query using LEFT OUTER JOINs to join the tables together.There are multiple counts in the query and count numbers are messed up.(if there are 4 records from one table and 3 from the other - it shows 12 as the count) Your help is appreciated.
View 2 Replies
View Related
Jan 26, 2004
How would I create a sql statement with 3 or 4 counts which would represent 3 or 4 different columns in a datagrid?
For example
SQL = "SELECT Count(department_id) as "totals1" FROM nonconformance WHERE department_id = '1'"
How would I make additional counts in this SQL statement that looks for when department_id=2 and 3 etc....
Thanks
View 7 Replies
View Related
Dec 8, 1999
I need to build a table to combine data into a single table but I need it to include a count on more than 1 column. For example, I have a table containing a store number, an isbn, an on hand quantity, and an on order quantity. It's probably easier to explain with an example. I have a source table containing this:
store isbn OnHand OnOrder
===== ========== ====== =======
104 0394572368 2 0
108 0394572368 0 1
109 0394572368 2 0
104 3321695545 2 1
108 3321695545 1 0
109 3321695545 3 1
And I need a table that will combine the isbns and give me sums
and counts like this:
Isbn OnHandLocations OnHandQty OnOrderLocations OnOrderQty
========== =============== ========= ================ ==========
0394572368 2 4 1 1
3321695545 3 6 2 2
Does anyone know of a query that can accomplish this? Thanks in advance.
View 1 Replies
View Related
Feb 13, 2006
I need a generic script that will query every table (minus system tables) within a database and generate record counts for each table.
Anybody have a script that will do this ???
Thanks in advance,
Nancy
View 6 Replies
View Related
