SQL Query to use in report builder 3.0: grouping results together

Question

I am trying to product a sales report that shows for example

itemid          salesdate       qty_sold
item1           1/9/14          3
item1           1/9/14          2
item1           1/9/14          5
item2           2/9/14          2
item3           4/9/14          1

The problem I have is because the sales of for example 3 on Monday could be made up of 1-3 different sales orders, I can only get it to show multiple lines and not group together as I want.

I want to product a report which shows item id, date of purchase and total sold on that day, not a list of all sales to include that item if that makes sense?

thanks in advance

more details:

    SELECT st.orderdate, sl.itemid, sl.qty
      FROM salestable st
INNER JOIN salesline sl
        ON salestable.salesid = salesline.salesid

currently, it displays results as follows.

OrderDate     ItemId     Qty    
1/1/14        101        1
1/1/14        101        3
1/1/14        102        1

I would like to group the rows if possible to only show 1 line per date & itemid. it doesn't work because they are obviously separate lines in the database as they have different order numbers etc.

OrderDate     ItemId    Qty
1/1/14        101       4
1/1/14        102       1
2/1/14        102       5
2/1/14        101       2

If it cant be done, then a grouping type within report builder would suffice but I cant see a way of doing it!

Cheers

Your question isn't very clear. Could you add some formatting, what is in your tables and expected output. I think you are looing for sum(order) group by day or something similar. — John Powell, Sep 24 '14 at 10:53

score 2 · Answer 1 · edited Sep 24 '14 at 12:10

2

If I understand it correctly, your problem is with sorting the record.

If that is the case, then append this to your SQL code.

ORDER BY salesdate DESC

edited Sep 24 '14 at 12:10

Mack

2,556
1
26
44

answered Sep 24 '14 at 08:02

Junaid

35
3

no the problem is... If i have 3 sales orders on the same day for 1 each, i want to show just 1 line, with ordered qty 3 by date. not 3 lines with 1 each. – JustAnAverageSQLuser Sep 24 '14 at 08:51

Mack · Accepted Answer · 2014-09-24T12:31:24.120

0

For the SQL you have included in your question you would need to use the aggregate function SUM with the GROUP BY statement.

    SELECT st.orderdate, sl.itemid, SUM(sl.qty)
      FROM salestable st
INNER JOIN salesline sl
        ON salestable.salesid = salesline.salesid
  GROUP BY st.orderdate, sl.itemid

You mention other fields that would prevent this, but don't include them in your example anywhere. If you could modify your question to include these we could come up with a definitive answer.

Based on your comment about the date, this is one possible solution:

    SELECT  DATEADD(dd, 0, DATEDIFF(dd, 0, st.orderdate)), sl.itemid, SUM(sl.qty)
      FROM salestable st
INNER JOIN salesline sl
        ON salestable.salesid = salesline.salesid
  GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, st.orderdate)), sl.itemid

edited Sep 24 '14 at 12:31

answered Sep 24 '14 at 12:16

Mack

2,556
1
26
44

although that isn't the exact query, using it I see why my problem is, its because the date field is date and time, and cant group by it as the times are different. ill need to convert the date and time field to show just date I think. – JustAnAverageSQLuser Sep 24 '14 at 12:23
What version of SQL are you using? – Mack Sep 24 '14 at 12:24
1

2012, its even with the format of the date, its still producing a line per sales order not grouping them as I hoped/expected. – JustAnAverageSQLuser Sep 24 '14 at 12:35
date itemid qty 17/07/2014 AJ1401110 1 17/07/2014 AJ1401110 1 17/07/2014 AJ1401110 1 – JustAnAverageSQLuser Sep 24 '14 at 12:36
sorry for terrible formatting, not a clue how to use this site for formatting text boxes! – JustAnAverageSQLuser Sep 24 '14 at 12:36
No problem, try the code in my answer above and let me know what happens. – Mack Sep 24 '14 at 12:37
2

no problems, if you need to set up examples for people I find that SQL Fiddle can really speed things up here's an example based on your question: http://sqlfiddle.com/#!3/3caf0 – Mack Sep 24 '14 at 12:44

score 0 · Answer 3 · edited May 23 '17 at 12:33

0

I have used this format for altering dates since I saw it on this SO post:

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

I hope this helps.

edited May 23 '17 at 12:33

Community

1
1

answered Sep 24 '14 at 12:33

ohoundj

63
1
8

SQL Query to use in report builder 3.0: grouping results together

3 Answers3