CODEWITHSUNDEEP
sqlsql-servert-sql
MichaelEaton
MichaelEaton

Reputation: 195

Multiple sql queries in one result

I'm struggling to figure out the best way to write this query to use less queries if possible.. I'm wondering if a pivot table might be the correct way?

My 3 separate queries:

SELECT
ISNULL(SUM(ps.UnitsSold), 0) AS UnitsSold,
ISNULL(pg.[Description], 'Other') AS [Description]
FROM dbo.ProductSales ps
LEFT OUTER JOIN dbo.Product p ON ps.ProductID = p.ProductID
LEFT OUTER JOIN dbo.ProductGroupings pg ON p.[Asin] = pg.[Asin] 
WHERE (ps.OrderDate BETWEEN GETDATE() - 10 AND GETDATE() - 3) AND     ps.DistributionCentreID IN (3)
GROUP BY pg.[Description], ps.DistributionCentreID

SELECT
ISNULL(SUM(ps.UnitsSold), 0) AS UnitsSold,
ISNULL(pg.[Description], 'Other') AS [Description]
FROM dbo.ProductSales ps
LEFT OUTER JOIN dbo.Product p ON ps.ProductID = p.ProductID
LEFT OUTER JOIN dbo.ProductGroupings pg ON p.[Asin] = pg.[Asin] 
WHERE (ps.OrderDate BETWEEN GETDATE() - 17 AND GETDATE() - 10) AND     ps.DistributionCentreID IN (3)
GROUP BY pg.[Description], ps.DistributionCentreID

SELECT
ISNULL(SUM(ps.UnitsSold), 0) AS UnitsSold,
ISNULL(pg.[Description], 'Other') AS [Description]
FROM dbo.ProductSales ps
LEFT OUTER JOIN dbo.Product p ON ps.ProductID = p.ProductID
LEFT OUTER JOIN dbo.ProductGroupings pg ON p.[Asin] = pg.[Asin] 
WHERE (ps.OrderDate BETWEEN GETDATE() - 374 AND GETDATE() - 367) AND     ps.DistributionCentreID IN (3)
GROUP BY pg.[Description], ps.DistributionCentreID

This produces results similar to this (first query):

 UnitsSold Description

 4154      desc1
 764       desc2

etc..

Things to think about, a description (product group) might not exist in one of the queries so I need to account for that.

Ideally I'd like it to look a little something like this:

Description UnitsSoldThisWeek UnitsSoldLastWeek UnitsSoldLastYear

Desc1       54                45                37

etc..

Any questions, issues or bitching due to bad query is acceptable, I'm happy to improve my understanding of SQL.

Thanks,

Michael

Upvotes: 1

Views: 85

Answers (3)

mohan111
mohan111

Reputation: 8865

;WITH CTE AS (
Select [UnitsSoldThisWeek],[UnitsSoldLastWeek],[UnitsSoldLastYear] FROM (
SELECT
ISNULL(SUM(ps.UnitsSold), 0) AS UnitsSold,
ISNULL(pg.[Description], 'Other') AS [Description]
FROM dbo.ProductSales ps
LEFT OUTER JOIN dbo.Product p ON ps.ProductID = p.ProductID
LEFT OUTER JOIN dbo.ProductGroupings pg ON p.[Asin] = pg.[Asin] 
WHERE (ps.OrderDate BETWEEN GETDATE() - 10 AND GETDATE() - 3) AND     ps.DistributionCentreID IN (3)
GROUP BY pg.[Description], ps.DistributionCentreID
UNION ALL
SELECT
ISNULL(SUM(ps.UnitsSold), 0) AS UnitsSold,
ISNULL(pg.[Description], 'Other') AS [Description]
FROM dbo.ProductSales ps
LEFT OUTER JOIN dbo.Product p ON ps.ProductID = p.ProductID
LEFT OUTER JOIN dbo.ProductGroupings pg ON p.[Asin] = pg.[Asin] 
WHERE (ps.OrderDate BETWEEN GETDATE() - 17 AND GETDATE() - 10) AND     ps.DistributionCentreID IN (3)
GROUP BY pg.[Description], ps.DistributionCentreID
UNION ALL
SELECT
ISNULL(SUM(ps.UnitsSold), 0) AS UnitsSold,
ISNULL(pg.[Description], 'Other') AS [Description]
FROM dbo.ProductSales ps
LEFT OUTER JOIN dbo.Product p ON ps.ProductID = p.ProductID
LEFT OUTER JOIN dbo.ProductGroupings pg ON p.[Asin] = pg.[Asin] 
WHERE (ps.OrderDate BETWEEN GETDATE() - 374 AND GETDATE() - 367) AND     ps.DistributionCentreID IN (3)
GROUP BY pg.[Description], ps.DistributionCentreID
)
PIVOT(MAX(UnitsSold)FOR Description IN([UnitsSoldThisWeek],[UnitsSoldLastWeek],[UnitsSoldLastYear])) )
)
Select [UnitsSoldThisWeek],[UnitsSoldLastWeek],[UnitsSoldLastYear] from CTE

i am not quite sure because lack of data but we can proceed like this also

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Do this with one query and conditional aggregation:

SELECT coalesce(pg.[Description], 'Other') AS [Description],
       sum(case when ps.OrderDate BETWEEN GETDATE() - 10 AND GETDATE() - 3 then UnitsSold
           end) as ThisWeek
       sum(case when ps.OrderDate BETWEEN GETDATE() - 17 AND GETDATE() - 10 then UnitsSold
                else 0
           end) as LastWeek,
       sum(case when ps.OrderDate BETWEEN GETDATE() - 374 AND GETDATE() - 367 then UnitsSold
                else 0
           end) as LastYear
FROM dbo.ProductSales ps LEFT OUTER JOIN
     dbo.Product p
     ON ps.ProductID = p.ProductID LEFT OUTER JOIN
     dbo.ProductGroupings pg
     ON p.[Asin] = pg.[Asin] 
WHERE ps.DistributionCentreID IN (3)
GROUP BY pg.[Description], ps.DistributionCentreID

Upvotes: 2

sarin
sarin

Reputation: 5307

i've changed your query a little to show a different technique which will combine your 3 queries. I may not have the brackets quite right round the SUMS but that gives you an idea. There are also better ways to do the sort of "date between" stuff you are trying, but that wasn't the question! See if that helps

SELECT
    ISNULL(SUM(ps.UnitsSold), 0) AS UnitsSold,
    ISNULL(pg.[Description], 'Other') AS [Description]
    SUM(CASE WHEN (ps.OrderDate BETWEEN GETDATE() - 10 AND GETDATE() - 3) AND ps.DistributionCentreID = 3 THEN 1 ELSE 0 END) AS UnitsSoldThisWeek 
    SUM(CASE WHEN (ps.OrderDate BETWEEN GETDATE() - 17 AND GETDATE() - 10) AND ps.DistributionCentreID = 3 THEN 1 ELSE 0 END) AS UnitsSoldThisWeek 
    SUM(CASE WHEN (ps.OrderDate BETWEEN GETDATE() - 374 AND GETDATE() - 367) AND ps.DistributionCentreID = 3 THEN 1 ELSE 0 END) AS UnitsSoldThisWeek 

FROM dbo.ProductSales ps
LEFT OUTER JOIN dbo.Product p ON ps.ProductID = p.ProductID
LEFT OUTER JOIN dbo.ProductGroupings pg ON p.[Asin] = pg.[Asin] 
GROUP BY pg.[Description], ps.DistributionCentreID

Upvotes: 1

Related Questions