April 3, 2008 at 12:51 pm
MY first ever post.
I have two tables
Table 1 contains Sales ID and User ID
I can get the total number of sales per User ID fine using the query
SELECT User ID, Count([Sales ID])
FROM Table 1
GROUP BY [Sales ID]
Table 2 Contains Detailed Items on Sales ID
eg
Table 1
User ID Sales ID
John Doe 001
Jane Doe 002
John Doe 003
Table 2
Sales ID Item Amount
001 hammer 5.00
001 saw 4.00
001 nails 0.20
002 screws 0.10
003 pliers 3.00
I want to write an sql query which returns the total number of Sales per user plus the total number of lines per user
so the above sample data would return the following
User ID Total Sales Total # of Items Sold Total Amount
John Doe 2 4 13.20
Jane Doe 1 1 0.10
Im at a loss on how to do it.
April 3, 2008 at 1:35 pm
I suspect this will do it - is this homework?
--== Create test table(s)
CREATE TABLE #table1
(
userid VARCHAR(10),
salesid CHAR(3)
)
CREATE TABLE #table2
(
salesid CHAR(3),
item VARCHAR(10),
amount MONEY
)
--== Create test data
INSERT INTO [#table1] ( [userid], [salesid] )
VALUES ( /* userid - VARCHAR(10) */ 'John Doe',
/* salesid - CHAR(3) */ '001' )
INSERT INTO [#table1] ( [userid], [salesid] )
VALUES ( /* userid - VARCHAR(10) */ 'Jane Doe',
/* salesid - CHAR(3) */ '002' )
INSERT INTO [#table1] ( [userid], [salesid] )
VALUES ( /* userid - VARCHAR(10) */ 'John Doe',
/* salesid - CHAR(3) */ '003' )
INSERT INTO [#table2]
(
[salesid],
[item],
[amount]
)
VALUES ( /* salesid - CHAR(3) */ '001',
/* item - VARCHAR(10) */ 'hammer',
/* amount - MONEY */ 5 )
INSERT INTO [#table2]
(
[salesid],
[item],
[amount]
)
VALUES ( /* salesid - CHAR(3) */ '001',
/* item - VARCHAR(10) */ 'saw',
/* amount - MONEY */ 4 )
INSERT INTO [#table2]
(
[salesid],
[item],
[amount]
)
VALUES ( /* salesid - CHAR(3) */ '001',
/* item - VARCHAR(10) */ 'nails',
/* amount - MONEY */ .2 )
INSERT INTO [#table2]
(
[salesid],
[item],
[amount]
)
VALUES ( /* salesid - CHAR(3) */ '002',
/* item - VARCHAR(10) */ 'screws',
/* amount - MONEY */ .1 )
INSERT INTO [#table2]
(
[salesid],
[item],
[amount]
)
VALUES ( /* salesid - CHAR(3) */ '003',
/* item - VARCHAR(10) */ 'pliers',
/* amount - MONEY */ 3 )
--== write the SQL
SELECT a.userid,
COUNT(a.SalesID),
COUNT(b.amount),
SUM(b.amount)
FROM [#table1] a
INNER JOIN [#table2] b ON a.[salesid] = b.[salesid]
GROUP BY [userid]
-- Cory
April 3, 2008 at 2:03 pm
John Doe should only have 2 sales, not 4.
SELECT a.userid,
COUNT(distinct a.salesid) as num_sales,
COUNT(b.amount) as num_items,
SUM(b.amount) as total_sales_amount
FROM #table1 as a
INNER JOIN #table2 as b ON a.salesid= b.salesid
GROUP BY a.userid
April 3, 2008 at 2:04 pm
I think COUNT(DISTINCT a.SalesID) is needed. Otherwise, the value is 4 instead of 2. At least that is what occurred on my test.
SELECT a.userid,
COUNT(DISTINCT a.SalesID),
COUNT(b.amount),
SUM(b.amount)
FROM [#table1] a
INNER JOIN [#table2] b ON a.[salesid] = b.[salesid]
GROUP BY [userid]
April 3, 2008 at 2:10 pm
:w00t:
Nice catch! I dont know why I missed that. I guess I grew to tired making my own data and table to finish verfiying the results. 😎
-- Cory
April 3, 2008 at 2:17 pm
Hi,
No its not homework its work work 🙂
thanks for your replies guys.
I was going wrong by not including distinct.
It seems to be working now but just need to validate it against my much larger dataset by doing some spot checks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply