Join tables by Column name

Question

I've got a table with vehicles mark and sales date. I need to take a query to take how many different vehicles has been sold in this year, separated by months, for example:

      | January | February | March | April |..............
----------------------------------------------------------
mark1 |         |     1    |   5   |       |..............
mark2 |   45    |          |       |   7   |..............
mark3 |   12    |    11    |   5   |   3   |..............

The original table is:

    mark    |    soldDate
----------------------------
    mark1   |  01/07/2020
    mark2   |  04/07/2020
    mark1   |  05/07/2020
    mark3   |  06/07/2020

If i want to take how many different vehicles has been sold i use this query:

SELECT mark, COUNT(mark) WHERE FORMAT(soldDate, 'MMMM') = 'january' GROUP BY mark

How can i divide the data in every single month?

you want the output as shown? if yes please share the raw data. — lovish kumar, Jul 06 '20 at 14:37
What does the data in your table(s) look like? What are *you* tried, and why didn't it work? What is your question? — Thom A, Jul 06 '20 at 14:39
The original table has a row for every vehicle sold and the sold date if i want to take the vehicles sold in a month: select mark, count(mark) as 'vehicles' where format(soldDate, 'MMMM') = 'JANUARY' group by mark — usr1990, Jul 06 '20 at 14:40
Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Filburt, Jul 06 '20 at 14:45

score 0 · Answer 1 · answered Jul 06 '20 at 14:48

With conditional aggregation:

select mark, 
  count(case when month(soldDate) = 1 then 1 end) as January,
  count(case when month(soldDate) = 2 then 1 end) as February,
  ...........................................................
where year(soldDate) = 2020 
group by mark

Mahesh Bhosale · Answer 2 · 2020-07-08T08:22:38.073

0

SELECT Mark, DATENAME(MONTH, DATEADD(MONTH, MONTH(SalesDate) - 1, '1900-01-01')) M, COUNT(*) COUNT
FROM VehicleSales  
WHERE YEAR(SalesDate) = '2020' 
GROUP BY Mark, MONTH(SalesDate)
Order by Mark, M

edited Jul 08 '20 at 08:22

answered Jul 06 '20 at 14:56

Mahesh Bhosale

47
10

1

Could you explain your answer a little bit, please? – Eduardo Yáñez Parareda Jul 07 '20 at 12:15

Join tables by Column name

2 Answers2