April 25, 2014 at 9:28 am
My testtable:
USE TestDB
GO
CREATE TABLE testtable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CompanyNumber nvarchar(50),
OrderDate date,
ProductOrdered nvarchar(50)
);
INSERT testtable
SELECT
101, '2014-04-22', 'ProdA' UNION ALL SELECT
101, '2014-04-22', 'ProdA' UNION ALL SELECT
101, '2014-04-22', 'ProdA' UNION ALL SELECT
101, '2014-04-23', 'ProdA' UNION ALL SELECT
102, '2014-04-22', 'ProdA' UNION ALL SELECT
102, '2014-04-22', 'ProdA' UNION ALL SELECT
102, '2014-04-22', 'ProdB' UNION ALL SELECT
103, '2014-04-22', 'ProdA' UNION ALL SELECT
103, '2014-04-23', 'ProdB' ;
A simple select:
SELECT CompanyNumber, OrderDate, ProductOrdered
FROM dbo.testtable
CompanyNumber OrderDate ProductOrdered
--------------- ---------- ---------------
101 2014-04-22 ProdA
101 2014-04-22 ProdA
101 2014-04-22 ProdA
101 2014-04-23 ProdA
102 2014-04-22 ProdA
102 2014-04-22 ProdB
102 2014-04-22 ProdB
103 2014-04-22 ProdA
103 2014-04-23 ProdB
There are two things that I'd like to count. One is the number of orders per day. The other is the number of each Product ordered per day.
Desired result
CompanyNumber OrderDate OrderCount ProdA ProdB
--------------- -------- ----------- ----- -----
101 2014-04-22 3 3 0
101 2014-04-23 1 1 0
102 2014-04-22 3 1 2
103 2014-04-22 1 1 0
103 2014-04-23 1 0 1
I think that I'm getting close.. But, where do I go from here.
SELECT CompanyNumber, COUNT (OrderDate) as OrderCount
FROM dbo.testtable
GROUP BY CompanyNumber, OrderDate
CompanyNumber OrderCount
--------------- -----------
101 3
102 3
103 1
101 1
103 1
April 25, 2014 at 9:34 am
Unless I missed something, you just have to add an order by clause.:cool:
April 25, 2014 at 9:48 am
Here's how to get the results you need:
SELECT CompanyNumber, ProductOrdered, OrderDate, COUNT(*) as OrderCount
FROM dbo.testtable
GROUP BY CompanyNumber, OrderDate, ProductOrdered
As you'll notice, they're not presented in the way you need. Many would argue that presentation should be done in the presentation layer, so you have two options:
(1) Read about PIVOTs and cross-tabs in order to denormalise your result set
(2) Let your front end do the pivoting. Excel, for example, can do that.
John
April 25, 2014 at 9:52 am
More than a simple order by is needed. You need to use CROSS TABS (or pivot if you want to complicate yourself :-P).
Here's a nice article on them: http://www.sqlservercentral.com/articles/T-SQL/63681/
And here's the example:
SELECT CompanyNumber,
OrderDate,
COUNT (ProductOrdered) as OrderCount,
COUNT( CASE WHEN ProductOrdered = 'ProdA' THEN ProductOrdered END) ProdA,
COUNT( CASE WHEN ProductOrdered = 'ProdB' THEN ProductOrdered END) ProdB
FROM dbo.testtable
GROUP BY CompanyNumber, OrderDate
ORDER BY CompanyNumber, OrderDate
April 25, 2014 at 10:00 am
Luis Cazares (4/25/2014)
More than a simple order by is needed. You need to use CROSS TABS (or pivot if you want to complicate yourself :-P)
I really did miss something (he he :Whistling:)
April 25, 2014 at 11:28 am
Luis Cazares (4/25/2014)
More than a simple order by is needed....]
Luis,
Thank you very much for the solution and for the reference.
April 25, 2014 at 11:51 am
inevercheckthis2002 (4/25/2014)
Luis Cazares (4/25/2014)
More than a simple order by is needed....]Luis,
Thank you very much for the solution and for the reference.
You're welcome. Be sure to understand how the code works and ask any questions that you have.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply