-3

I'm working SQLServer 2019 and I have orders table. Orders table has columns orderid, userid, country, site, count, price. I need help write query. You can see below with details.

Question: Show me that how much user ordered from 2010 to present once, twice, etc in 5 stripe ([1], [2], [3], [4-10], [10-100]) based on country

Example Result:

Country     1   2   3   4-10    10-100
---------------------------------------------------------
US          0   0   3   4   5
GB          10  10  8   50  60
NL          20  20  20  100 30
....

My query: I used pivot table from 1 to 3 and I have correct result. But, I couldn't write the ranges ten to four and ten to one hundred in the pivot table.

I ran query for below;

select * from (
SELECT Country,
count(*) as total,
count as totalpay
FROM [CRM].[dbo].[Orders]
where date like '%2010%'
group by Country,count
) countrytotalcnt
pivot
(
sum(total) for totalpay in ([1],[2],[3],[4-10],[10-100])
)countrytotal;

I have error for below;

Msg 8114, Level 16, State 1, Line 24
Error converting data type nvarchar to int.
Msg 473, Level 16, State 1, Line 24
The incorrect value "4-10" is supplied in the PIVOT operator.

Completion time: 2021-10-13T13:55:47.1067875+03:00
user17141323
  • 1
  • 1
  • 1
    Why do we need to show you how much user ordered from 2010 to present? Surely that's the question you need to answer, not us (reads like homework to me). What's wrong with the solution you have? What are you *really* asking us? (No, asking us to do your homework isn't ok.) – Thom A Oct 13 '21 at 09:37
  • Not is homework. It is technical questions and my manager asked and is waiting for my answer. I'm looking for clues, I've already done nine out of ten questions myself. I do not know only a small part of 1 of them, I asked you to guide me. thanks. – user17141323 Oct 13 '21 at 09:41
  • Ok, so it's your work work, but what are you asking? We aren't here to do your work for you. What are you having difficulty with? What about what you have done isn't working? See my initial comment, it still applies. – Thom A Oct 13 '21 at 09:53
  • I wrote in my question. I want to show ranges as columns. I want to do it using pivot table. example: it doesn't work. I want to show total sales between four and ten, between ten and one hundred. Part of the sample query: totalpayin ([1],[2],[3],[4-10],[10-100]) – user17141323 Oct 13 '21 at 10:18
  • 1
    "I want" isn't a question... What does "doesn't work" mean? Do you get an error? Unexpected results? Undesired behaviour? Causes your server to catch fire? Help us help you. – Thom A Oct 13 '21 at 10:47
  • So you don't know what doesn't work mean? Then we can't help you... Presumably you need to find out from the person who told you it "doesn't work" what "doesn't work" means, then you can explain that here. – Thom A Oct 13 '21 at 11:02
  • Question updated. You can see in my question. – user17141323 Oct 13 '21 at 11:03
  • 1
    I can't ask questions, I'm expected to solve it myself. – user17141323 Oct 13 '21 at 11:04
  • I would suggest looking into conditional aggregation, rather than using the very restrictive `PIVOT` operator. Also `where date like '%2010%'` is going to generate an error too; `%2010%` isn't a valid date and time value; use proper date boundaries. – Thom A Oct 13 '21 at 11:17
  • There is no problem with the where condition. If you know how to use the pivot condition, you can help or suggest another solution according to the desired result. – user17141323 Oct 13 '21 at 11:20
  • *"There is no problem with the where condition."* Then there is a problem with the design and you are storing dates as a `varchar` value; a much bigger problem. *"or suggest another solution"* I did, look into conditional aggregation. – Thom A Oct 13 '21 at 11:22

1 Answers1

0

As I mentioned in the comments, use conditional aggregation here, it's far more versatile that the PIVOT operator.

Also, your WHERE will error as '%2010%' cannot be converted to a date and time data type. If the WHERE is "working" the problem is your design and you are storing date and time values as a string based data type; a fatal flaw and needs to be fixed. varchar is NOT a one size fits all data type. I assume your database isn't fundamentally flawwed and use a date boundaries.

I can't test this, as we have no sample data, but this is likely what you want. Note the comments in the SQL as well (especially on your ranges 4-10 and 10-100).

WITH Counts AS(
    SELECT O.Country,
           COUNT(*) AS Total
           O.[Count] AS TotalPay--COUNT is a reserved word, I suggest against naming your columns this
    FROM dbo.Orders O
    WHERE [date] >= '20200101'
      AND [date] < '20210101'
    GROUP BY O.Country)
SELECT C.Country,
       SUM(CASE TotalPay WHEN 1 THEN Total END) AS [1],
       SUM(CASE TotalPay WHEN 2 THEN Total END) AS [2],
       SUM(CASE TotalPay WHEN 3 THEN Total END) AS [3],
       SUM(CASE WHEN TotalPay BETWEEN 4 AND 10 THEN Total END) AS [4-10], --note this includes 10
       SUM(CASE WHEN TotalPay BETWEEN 10 AND 100 THEN Total END) AS [10-100] --Note this includes 10 again
FROM Counts C
GROUP BY C.Country;
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • If it answers the question, @user17141323 , then please accept it as the solution so that future users know it was helpful. – Thom A Oct 13 '21 at 11:53