SQL Query Help

Other SQL Server Topics
1

I’m looking for a little assistance. I have a table called equipment. One row is an order of some type of equipment.

Here are the fields:

num_id date player_id order_id active jersey comment
BIGINT DATE BIGINT BIGINT CHAR(1) CHAR(3) VARCHAR(1024)
11 2018-01-01 123 1 Y XL
11 2018-01-01 123 2 Y M Purple
11 2018-01-01 123 3 Y L White, Red
13 2018-01-11 456 1 N S Yellow, Light Blue
14 2018-02-01 789 1 Y M Orange, Black
15 2018-02-02 101 1 Y XL Shield
15 2018-02-02 101 2 Y XL Light Green, Grey

I need to write a query that shows one row for each month with the columns Month Total Orders Total Products ordered And one extra column for a total count of each size sold.

Is this easy? Any help would be appreciated.

SQL Server is the dbms. As well, I am struggling as I don't know how to get the month from a date. And then adding the column for size counts has me baffled, but I haven't fully investigated that portion. I feel like the rest I have done individually, just never did it in one succinct query.

The end goal I think would be like this:

Month Total Orders Total Products Ordered Size Count

January 1 3 S-0, M-1, L-1, XL-2

February 3 6 S–1, M–2, L–1, XL–3

Or this:

Month Total Orders Total Products Ordered S Count M Count L Count XL Count

January 1 3 0 1 1 2

February 3 6 1 2 1 3

Thanks in advance!

2

use the function MONTH(date)

and DATENAME() for the month name

3 
CREATE TABLE equipment(num_id bigint, date date, player_id bigint,order_id bigint,active char(1), jersey char(3), comment varchar(1024));
INSERT dbo.equipment
   ( num_id, [date], player_id, order_id, active, jersey, comment )
VALUES 
   ( 11, '2018-01-01', 123, 1, 'Y', 'XL', '' )
 , ( 11, '2018-01-01', 123, 2, 'Y', 'M', 'Purple' )
 , ( 11, '2018-01-01', 123, 3, 'Y', 'L', 'White, Red' )
 , ( 13, '2018-01-11', 456, 1, 'N', 'S', 'Yellow, Light Blue' )
 , ( 14, '2018-02-01', 789, 1, 'Y', 'M', 'Orange, Black' )
 , ( 15, '2018-02-02', 101, 1, 'Y', 'XL', 'Shield' )
 , ( 15, '2018-02-02', 101, 2, 'Y', 'XL', 'Light Green, Grey' );
SELECT 
     DateName(MONTH, [date]) [Month]
   , Month([e].[date]) MonthNum
   , Count(*) [Total Orders]
   , Sum(CASE WHEN e.jersey = 'S' THEN 1 ELSE 0 END) [Count S]
   , Sum(CASE WHEN e.jersey = 'M' THEN 1 ELSE 0 END) [Count M]
   , Sum(CASE WHEN e.jersey = 'L' THEN 1 ELSE 0 END) [Count L]
   , Sum(CASE WHEN e.jersey = 'XL' THEN 1 ELSE 0 END) [Count XL]   
FROM dbo.equipment e
GROUP BY DateName(MONTH, [date]), Month([e].[date])
ORDER BY Month([e].[date]);
DROP TABLE dbo.equipment;

image

2 Likes
4

Thanks so much for your assistance!