0

My query, which kind of works is as follows:

SELECT [copyright status],
sum(IIF(layer='key info',1,0)) AS [Key Info],
sum(IIF(layer='approaches',1,0)) AS [Approaches],
sum(IIF(layer='research',1,0)) AS [Research]
FROM resources
WHERE (IIF(literacy,1,0)) OR (IIF(numeracy,1,0)) OR (IIF(poverty,1,0))
GROUP BY [copyright status]

UNION

SELECT null,
sum(IIF(layer='key info',1,0)) AS [Key Info],
sum(IIF(layer='approaches',1,0)) AS [Approaches],
sum(IIF(layer='research',1,0)) AS [Research]
FROM resources
WHERE (IIF(literacy,1,0)) OR (IIF(numeracy,1,0)) OR (IIF(poverty,1,0))

UNION

SELECT [lw status],
sum(IIF(layer='key info',1,0)) AS [Key Info],
sum(IIF(layer='approaches',1,0)) AS [Approaches],
sum(IIF(layer='research',1,0)) AS [Research]
FROM resources
WHERE (IIF(literacy,1,0)) OR (IIF(numeracy,1,0)) OR (IIF(poverty,1,0)) AND [lw status] = 'In Reserve'
GROUP BY [lw status]

UNION

SELECT [lw status],
sum(IIF(layer='key info',1,0)) AS [Key Info],
sum(IIF(layer='approaches',1,0)) AS [Approaches],
sum(IIF(layer='research',1,0)) AS [Research]
FROM resources
WHERE (IIF(literacy,1,0)) OR (IIF(numeracy,1,0)) OR (IIF(poverty,1,0)) AND [lw status] = 'Published'
GROUP BY [lw status];

The WHERE clause in all the four parts of this query is trying to determine the three mentioned checkboxes (literacy, numeracy or poverty) are checked. Any combination of the three can be checked for the results that I would like.

In principle, the query works. However, the output returns two results for the third part and two results for the fourth part.

If I run the query with just one checkbox defined, so:

WHERE (IIF(literacy,1,0)) [lw status] = 'In Reserve'

The query works fine, its just adding in one or more of these conditions seems to cause the problems.

I've also tried defining true values by using =-1 which returns the same problems.

Many thanks.

KurtB89
  • 49
  • 1
  • 4

1 Answers1

2

See if this is more clear. You don't need the IIF function to check a Yes/No value at the WHERE clauses. Aditionally, parenthesis are required to express the logic: (x OR y OR z) AND w

SELECT null as Status,
sum(IIF(layer='key info',1,0)) AS [Key Info],
sum(IIF(layer='approaches',1,0)) AS [Approaches],
sum(IIF(layer='research',1,0)) AS [Research]
FROM resources
WHERE (literacy OR numeracy OR poverty)

UNION

SELECT [copyright status],
sum(IIF(layer='key info',1,0)) AS [Key Info],
sum(IIF(layer='approaches',1,0)) AS [Approaches],
sum(IIF(layer='research',1,0)) AS [Research]
FROM resources
WHERE (literacy OR numeracy OR poverty)
GROUP BY [copyright status]

UNION

SELECT [lw status],
sum(IIF(layer='key info',1,0)) AS [Key Info],
sum(IIF(layer='approaches',1,0)) AS [Approaches],
sum(IIF(layer='research',1,0)) AS [Research]
FROM resources
WHERE (literacy OR numeracy OR poverty) AND [lw status] = 'In Reserve'
GROUP BY [lw status]

UNION

SELECT [lw status],
sum(IIF(layer='key info',1,0)) AS [Key Info],
sum(IIF(layer='approaches',1,0)) AS [Approaches],
sum(IIF(layer='research',1,0)) AS [Research]
FROM resources
WHERE (literacy OR numeracy OR poverty) AND [lw status] = 'Published'
GROUP BY [lw status];

Regards

Clon
  • 1,035
  • 8
  • 12
  • Marvellous, thank you and thank you for explaining it to. The long road to learning SQL continues. – KurtB89 Jun 21 '13 at 08:30