0

This is a follow-up to my previous question: Get records for last 10 dates

I have to generate reports for all books of a store along with sold count (if any) for the last N dates, by passing storeId.

   BOOK               Book Sold                   Store
 ------------        --------------------        ---------------- 
 Id  Name  SID     Id Bid Count Date            SID  Name
  1   ABC   1      1   1   20  11/12/2015        1    MNA
  2   DEF   1      2   1   30  12/12/2015        2    KLK
  3   DF2   2      3   2   20  11/12/2015        3    KJH
  4   DF3   3      4   3   10  13/12/2015
  5   GHB   3      5   4    5  14/12/2015

The number of days N is supplied by the user. This is the expected output for the last 4 dates for storeId -1,2 & 3.

 BookName  11/12/2015 12/12/2015  13/12/2015  14/12/2015
  ABC         20        30         --             --    
  DEF         20        --         --             -- 
  DF2         --        --         10             -- 
  DF3         --        --         --              5
  GHB         --        --         --             --

If the user passes 5 than data for the last 5 days shall be generated, starting date as 14/12/2015.

I am using Postgres 9.3.

Community
  • 1
  • 1
manthan davda
  • 319
  • 1
  • 3
  • 13
  • Please *always* provide your Postgres version. And add a proper table definition showing exact data types and constraints. [I have asked the same for your previous question](http://stackoverflow.com/q/32905410/939860) ... – Erwin Brandstetter Oct 05 '15 at 00:03
  • Yeah Sorry I forgot to add taht.. – manthan davda Oct 05 '15 at 04:32
  • So will you add the missing table definitions? Complete `CREATE TABLE` script or what you get with `\d book` and `\d book_sold` in psql (including all relevant columns). – Erwin Brandstetter Oct 05 '15 at 21:13

1 Answers1

-1

Cross table without crosstab function:

SELECT
SUM(CASE book.Date ='11/11/2015' THEN book.Count ELSE 0 END) AS '11/11/2015',
SUM(CASE book.Date ='15/11/2015' THEN book.Count ELSE 0 END) AS '15/11/2015',
SUM(CASE book.Date ='17/11/2015' THEN book.Count ELSE 0 END) AS '17/11/2015'
FROM
store,
book
WHERE
store.Id = booksold.Bid
AND store.Id IN (1,2)
GROUP BY
book.Name
ORDER BY
book.id ASC;
phsaires
  • 2,188
  • 1
  • 14
  • 11