Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

counting / group by confusion Expand / Collapse
Author
Message
Posted Friday, April 25, 2014 9:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 9:13 AM
Points: 80, Visits: 341
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

Post #1565143
Posted Friday, April 25, 2014 9:34 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:19 PM
Points: 1,575, Visits: 4,320
Unless I missed something, you just have to add an order by clause.
Post #1565148
Posted Friday, April 25, 2014 9:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 5,308, Visits: 9,700
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
Post #1565157
Posted Friday, April 25, 2014 9:52 AM This worked for the OP Answer marked as solution


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 3,511, Visits: 7,560
More than a simple order by is needed. You need to use CROSS TABS (or pivot if you want to complicate yourself ).
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




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1565158
Posted Friday, April 25, 2014 10:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:19 PM
Points: 1,575, Visits: 4,320
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 )

I really did miss something (he he )
Post #1565161
Posted Friday, April 25, 2014 11:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 9:13 AM
Points: 80, Visits: 341
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.



Post #1565199
Posted Friday, April 25, 2014 11:51 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 3,511, Visits: 7,560
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.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1565208
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse