3

My query looks like this:

SELECT mthreport.*
FROM crosstab
('SELECT 
to_char(ipstimestamp, ''mon DD HH24h'') As row_name, 
varid::text || log.varid || ''_'' || ips.objectname::text As bucket, 
COUNT(*)::integer As bucketvalue
FROM loggingdb_ips_boolean As log 
INNER JOIN IpsObjects As ips 
ON log.Varid=ips.ObjectId
WHERE ((log.varid = 37551) 
OR (log.varid = 27087) 
OR (log.varid = 50876)
OR (log.varid = 45096)
OR (log.varid = 54708)
OR (log.varid = 47475)
OR (log.varid = 54606)
OR (log.varid = 25528)
OR (log.varid = 54729))
GROUP BY to_char(ipstimestamp, ''yyyy MM DD HH24h''), row_name, objectid, bucket        
ORDER BY to_char(ipstimestamp, ''yyyy MM DD HH24h''), row_name, objectid, bucket' )

As mthreport(item_name text,  varid_37551 integer, 
            varid_27087  integer , 
            varid_50876  integer , 
            varid_45096  integer , 
            varid_54708  integer , 
            varid_47475  integer , 
            varid_54606  integer , 
            varid_25528  integer , 
            varid_54729  integer , 
            varid_29469 integer)

the query can be tested against a test table with this connection string: "host=bellariastrasse.com port=5432 dbname=IpsLogging user=guest password=guest"

The query is syntactically correct and runs fine. My problem is that it the COUNT(*) values are always filling the leftmost column. however, in many instances the left columns should have a zero, or a NULL, and only the 2nd (or n-th) column should be filled. My brain is melting and I cannot figure out what is wrong!

aag
  • 680
  • 2
  • 12
  • 33

1 Answers1

2

The solution for your problem is to use the crosstab() variant with two parameters.

The second parameter (another query string) produces the list of output columns, so that NULL values in the data query (the first parameter) are assigned correctly.

Check the manual for the tablefunc extension, and in particular crosstab(text, text):

The main limitation of the single-parameter form of crosstab is that it treats all values in a group alike, inserting each value into the first available column. If you want the value columns to correspond to specific categories of data, and some groups might not have data for some of the categories, that doesn't work well. The two-parameter form of crosstab handles this case by providing an explicit list of the categories corresponding to the output columns.

Emphasis mine. I posted a couple of related answers recently here or here or here.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you Erwin, kiss-de-hoand gnä'herr (='you da man', loosely translated)! I shall be trying the 2-parameter query and report back! – aag Jun 18 '12 at 13:18
  • Bingo - I set up the second query per Erwin's instructions, and now things work like a charm! Next I will try using a function to provide the category fields. It would appear to me that the FUNCTION way is a nice workaround that enables the generation of "dynamic" crosstabs whose categories may not be known a priori. Am I right? – aag Jun 18 '12 at 13:54
  • 1
    @aag: "Küss die Hand gnä' Frau!" would be the old-school Viennese greeting, but really only targeted at women - hand-kissing among same-sex people in the public wasn't en-vogue in the old Austrian empire. :D – Erwin Brandstetter Jun 18 '12 at 17:09
  • @aag: About the dynamic function: this can only be done as long as the **return type is the same**. Else you'll have to return records with unknown columns - which brings you back to where you started. – Erwin Brandstetter Jun 18 '12 at 17:11
  • I still have a lot of trouble trying to generate a function. The pivot should be on the calculated field **varid::text || log.varid || ''_'' || ips.objectname::text** FROM loggingdb_ips_boolean As log INNER JOIN IpsObjects As ips ON log.Varid=ips.ObjectId; but I really cannot figure out how to program a function that will pull out these values. Can you give me a hand? – aag Jun 18 '12 at 20:21