Results 1 to 6 of 6

Thread: Tricky Query

  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: Tricky Query

    I have a data table like this:

    Code:
    MachineID	Product		Sales
    -------------------------------
    1			Magazine	$20.00
    1			Drink		$30.00
    2			Drink		$30.00
    3			Magazine	$30.00
    3			Drink		$40.00
    4			Magazine	$30.00
    5			Food		$40.00
    5			Drink		$30.00
    6			Drink		$40.00
    One of the reports the user needs to see looks like this:
    Code:
    Product				Number of Machines	Total Sales
    Magazine/Drink		2					$120.00
    Drink				2					$70.00
    Magazine			1					$30.00
    Food/Drink			1					$70.00
    To clarify:

    There are two magazine/drink machines (ID 1 and 3)
    There are two drink only machines (ID 2 and 6)
    There is one magazine only machine (ID 4)
    There is one food and drink machine (ID 5)

    How do I do this query?

    Ideally, I wouldn't limit the number of products in a given machine, but I can do so if necessary.

    I'm using SQL Server 2000 so I can't use the newer PIVOT/UNPIVOT functions in SQL Server 2005.

    Here is some setup T-SQL code:

    Code:
    CREATE TABLE SalesData
    (
    	MachineID	INTEGER,
    	ProductName	VARCHAR(50),
    	Sales		MONEY
    )
    
    INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (1, 'Magazine', 20)
    INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (1, 'Drink', 30)
    INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (2, 'Drink', 30)
    INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (3, 'Magazine', 30)
    INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (3, 'Drink', 40)
    INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (4, 'Magazine', 30)
    INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (5, 'Food', 40)
    INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (5, 'Drink', 30)
    INSERT INTO SalesData (MachineID, ProductName, Sales) VALUES (6, 'Drink', 40)
    Of course, this is a much simplified version of the real business problem I'm facing. Any help is greatly appreciated. Thanks!

  2. #2
    Join Date
    Feb 2007
    Posts
    62
    You don't have enough data on machines here. I think you need to introduce a new table describing the types of machine (machine type, description) and then link the individual machines back to there.
    Then just join up the tables & count/sum as appropriate.

  3. #3
    Join Date
    Feb 2007
    Posts
    62
    Actually to do it properly you need these tables:
    MachineType(type, description)
    Machine(id, type)
    SalesData

  4. #4
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    Code:
    create function CombineProd (@McId int)
    returns varchar(100) 
    as
    begin
    	declare @Retu as varchar(100)
    	set @Retu = ''
    	select @Retu = @Retu + '/' + ProductName  from SalesData where MachineId=@McId
    	set @Retu = right(@Retu,len(@Retu)-1)
    	return @Retu
    end
    GO
    ----------
    
    select dbo.CombineProd(MachineId) as Prod, count(distinct MachineId) as NoOfMc, sum(Sales) as TotSales from SalesData group by dbo.CombineProd(MachineId)

  5. #5
    Join Date
    Oct 2003
    Posts
    268
    LoztInSpace, that would make things easier but isn't 100% practical. One reason is that machines change over time. Often a machine will sell just drinks for one date range, then sell drinks/food for another date range. This report mus accurately report this scenario.

    upalsen, thank you. That function/query worked perfectly. That function uses an interesting trick to work. I like that there is no limit on the number of products.

    Someone else recommended a slightly different solution:

    Code:
    SELECT
    	MachineSalesData.ProductName1 + COALESCE('/' + MachineSalesData.ProductName2, '') AS CombinedProductName
    	, COUNT(*) AS NumMachines
    	, SUM(Sales) AS Sales
    FROM
    	(SELECT
    		MachineID, SUM(Sales) AS Sales
    		, MIN(CASE WHEN Sequence = 1 THEN ProductName END) AS ProductName1
    		, MIN(CASE WHEN Sequence = 2 THEN ProductName END) AS ProductName2
    	FROM
    		(SELECT
    			SalesData.MachineID, SalesData.ProductName, SUM(SalesData.Sales) AS Sales
    			, (SELECT COUNT(DISTINCT ProductName) FROM SalesData AS SequenceTable
    				WHERE SequenceTable.MachineID = SalesData.MachineID AND SequenceTable.ProductName <= SalesData.ProductName) AS Sequence
    		FROM SalesData
    		GROUP BY SalesData.MachineID, SalesData.ProductName
    		) AS SalesWithSequence
    	GROUP BY MachineID
    	) AS MachineSalesData
    GROUP BY MachineSalesData.ProductName1 + COALESCE('/' + MachineSalesData.ProductName2, '')

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No limit except that the output is defined as varchar(100). I'd bump this up much higher if I were you.

    But between upalsen's method and the method you posted, which one you implement depends upon whether you are trying to get the project done quickly, or you are getting paid by the lines of code you generate...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •