August 26, 2009 at 1:37 pm
Hi,
I am learning SQL, and want to make some application code more database orientated.
I am looking for someone to help me understand and learn some methodology rather than just an ad hoc solution. Therefore, whilst the real application data is a lot more complex, perhaps someone can tell me how I might acheive the following so I can apply the theory elsewehere.
Lets say there is a table with some non specific data.
CID Startnumber Endnumber Type
Smith 10 15 1
Smith 12 18 1
Smith 10 17 2
Smith 15 19 2
Jones 12 18 2
Jones 11 13 1
What I want to learn how to do is to create a statement that will return the difference between the Start and End numbers for each client. Also throw in perhaps a filter by type. For example If I ran the statement with the query to specify type 1 then I would want a result set like:
Smith 11
Jones 2
Hope this makes sense, and any help much appreciated.
Matt
August 26, 2009 at 1:50 pm
I went ahead and generated the create table script and your sample data. I think this is what you are looking for. The top part just creates the sample data. The logic you are asking about is in the select statement. If you have any questions just ask.
CREATE TABLE #temp (cid VARCHAR(6), startNumber INT, endNumber INT, TYPE INT)
INSERT INTO #temp
SELECT 'Smith', 10, 15, 1
UNION ALL
SELECT 'Smith', 12, 18, 1
UNION ALL
SELECT 'Smith', 10, 17, 2
UNION ALL
SELECT 'Smith', 15, 19, 2
UNION ALL
SELECT 'Jones', 12, 18, 2
UNION ALL
SELECT 'Jones', 11, 13, 1
SELECT cid, SUM(endNumber-startNumber) FROM #temp
WHERE TYPE = 1
GROUP BY cid
DROP TABLE #temp
August 26, 2009 at 2:09 pm
Thanks for your help Matt
I am very grateful. An added complication, what if there is another table called CUSTOMER, and there are is the same CID column. What is I want to include some data from that table, for example Cnumber, I can't simply add it into the select statment with a join as it would throw an error as it is not part of the aggregate function ?
August 26, 2009 at 2:23 pm
I am not really sure what you are trying to do. Is the data such that 1 cid corresponds to 1 cnumber in the customer table? If so then you could join to that table and add the column you want to both the select statement and the group by clause. I didn't test this syntax as I didn't create the other table but it should be right.
SELECT #temp.cid, cnumber, SUM(endNumber-startNumber)
FROM #temp
inner join customer on (#temp.cid = customer.cid)
WHERE TYPE = 1
GROUP BY #temp.cid, cnumber
Sorry, I just noticed a syntax error. Fixed now
August 26, 2009 at 2:38 pm
Once again, thank you for your time.
The cnumber would be something different to the CID. The actual application is that we have a customer table with unique a unique CID field per record, we then have a seperate table with jobs start value and end value. The jobs table has alot of entries with the CID value repeated, like in my example in the first post. The report I'm trying to get would get the total of the difference between start and end of the jobs, but also other data from the customer table.
For example
CID JOB value Total CNUMBER
SMITH 11 SMITH123
Hope that this makes sense
Matt
August 26, 2009 at 2:42 pm
I think the above query will work. Just add all of the elements from the customer table to both the select and the group by clause.
SELECT #temp.cid, cnumber, otherColumn, OtherColumn2, OtherColumn3, SUM(endNumber-startNumber)
FROM #temp
INNER JOIN #customer ON #temp.cid = #customer.cid
WHERE TYPE = 1
GROUP BY #temp.cid, cnumber, otherColumn, otherColumn2, otherColumn3
August 26, 2009 at 2:57 pm
Yes, just realised this by playing with it. I thought it would try and group by every variable, I didn't realise it would work out the group by primary key unaided
Thanks alot.
Matt
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply